public virtual ActionResult GetReportSnapshot(string fileName, string sourceName, string sourceData)
{
PrintModel model = new PrintModel();
model.fileName = fileName;
model.sourceName = "Source";
model.sourceData = null;
if (fileName == "packListB")
{
model.sourceData = BLLFactory <PackList_Head> .Instance.GetPrint(sourceData);
model.sourceName = sourceName;
model.fileName = fileName;
}
// 创建报表对象
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
//读取文件
report.Load(Server.MapPath(string.Format("~/Content/reports/{0}.mrt", model.fileName)));
//截止读取
report.Compile();
if (!string.IsNullOrEmpty(model.sourceName) && model.sourceData != null)
{
//注册数据源P
report.RegData(model.sourceName, model.sourceData);
}
//返回报表
return(Stimulsoft.Report.Mvc.StiMvcViewer.GetReportSnapshotResult(report));
}
protected void SetandoRelExcel()
{
Stimulsoft.Report.StiReport Report = new Stimulsoft.Report.StiReport();
DataTable dt = new DataTable();
string nomeRelatorio = string.Empty;
if (Convert.ToString(Request.QueryString["Rel"]) == "frmFechamentoRel")
{
FechamentoFolhaSrv fechamentoFolhaSrv = new FechamentoFolhaSrv();
dt = new DataTable();
dt = fechamentoFolhaSrv.GetDadosImprimir(Request.QueryString["Mes"].ToString());
nomeRelatorio = "FechamentoFolha.xls";
Report.Load(Server.MapPath(@"~/Relatorio/STF/FechamentoFolha.mrt"));
Report.Compile();
}
Report.RegData("DADOS", dt);
Report.Render();
System.IO.MemoryStream memStream = new System.IO.MemoryStream();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/xls";
HttpContext.Current.Response.AddHeader(nomeRelatorio, "");
Stimulsoft.Report.Export.StiExcelExportService ExportXSL = new Stimulsoft.Report.Export.StiExcelExportService();
ExportXSL.ExportExcel(Report, memStream, Stimulsoft.Report.StiPagesRange.All, false, false, true, true);
Response.ContentType = "application/xls";
Response.AddHeader("content-disposition", "inline; filename=" + nomeRelatorio + "");
Response.BinaryWrite(memStream.ToArray());
}
protected void Page_Load(object sender, EventArgs e)
{
MetodosPontoFrequencia.Frequencia Freq = new MetodosPontoFrequencia.Frequencia();
MetodosPontoFrequencia.DataSetPontoFrequencia ds = new DataSetPontoFrequencia();
MetodosPontoFrequencia.PreencheTabela pt = new MetodosPontoFrequencia.PreencheTabela();
Freq.HorasDias(7, 688, 2014, ds, 4, 7);
Stimulsoft.Report.StiReport Report = new Stimulsoft.Report.StiReport();
//Stimulsoft.Report.Web.StiWebDesigner st1 = new Stimulsoft.Report.Web.StiWebDesigner();
Report.Load(Server.MapPath(@"~/Relatorio/RPT/ReportTesteFeito.mrt"));
Report.RegData("DatasetPontoFrequencia", ds);
Report.Render();
MemoryStream memStream = new MemoryStream();
Stimulsoft.Report.Export.StiPdfExportService Export = new Stimulsoft.Report.Export.StiPdfExportService();
Export.ExportPdf(Report, memStream, Stimulsoft.Report.StiPagesRange.All, 100, 100, false, false, true, true, "", "", Stimulsoft.Report.Export.StiUserAccessPrivileges.All, Stimulsoft.Report.Export.StiPdfEncryptionKeyLength.Bit128, false);
//HttpContext.Current.Response.ClearContent();
//HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.BinaryWrite(memStream.ToArray());
//HttpContext.Current.Response.Flush();
//HttpContext.Current.Response.Close();
}
public void OnRunningSalesAndBuyInvoicesReport(Entity reportEntity, Stimulsoft.Report.StiReport report, Dictionary <string, object> parameters)
{
var reportDataTable = new DataTable("ReportTable");
var invoiceType = parameters["InvoiceType"] as string;
EntityList salesAndBuysList = new EntityList();
//var accountReviewType = parameters["AccountReviewType"];
reportDataTable.Columns.Add("IssueDate");
reportDataTable.Columns.Add("DocType_PersianText");
reportDataTable.Columns.Add("InvoiceNumber");
reportDataTable.Columns.Add("TotalStuffAndServicesPrice", typeof(decimal));
reportDataTable.Columns.Add("GeneralDiscount", typeof(decimal));
reportDataTable.Columns.Add("StuffAndServicesTotalAmountAfterDiscount", typeof(decimal));
reportDataTable.Columns.Add("TotalTaxAndToll", typeof(decimal));
reportDataTable.Columns.Add("FinalAmount", typeof(decimal));
for (int i = 0; i < ps.SalesAndBuyInvoices.Entities.Count; i++)
{
Entity saleAndBuyEntity = ps.SalesAndBuyInvoices.Entities[i];
var docType = saleAndBuyEntity.GetFieldValue <string>("DocType");
if (docType == invoiceType)
{
salesAndBuysList.Entities.Add(saleAndBuyEntity);
}
}
foreach (var entity in salesAndBuysList.Entities)
{
var row = reportDataTable.NewRow();
row["IssueDate"] = entity.GetFieldValue <string>("IssueDate");
row["DocType_PersianText"] = entity.GetFieldValue <string>("DocType_PersianText");
row["InvoiceNumber"] = entity.GetFieldValue <string>("InvoiceNumber");
row["TotalStuffAndServicesPrice"] = entity.GetFieldValue <decimal>("TotalStuffAndServicesPrice");
row["GeneralDiscount"] = entity.GetFieldValue <decimal>("GeneralDiscount");
row["StuffAndServicesTotalAmountAfterDiscount"] = entity.GetFieldValue <decimal>("StuffAndServicesTotalAmountAfterDiscount");
row["TotalTaxAndToll"] = entity.GetFieldValue <decimal>("TotalTaxAndToll");
row["FinalAmount"] = entity.GetFieldValue <decimal>("FinalAmount");
reportDataTable.Rows.Add(row);
}
report.Dictionary.DataSources.Clear();
report.RegData("DataSource1", reportDataTable);
report.Dictionary.Synchronize();
StiDataBand dataBand1 = (StiDataBand)report.GetComponentByName("DataBand1");
dataBand1.DataSourceName = "ReportTable";
//report.DataSources.Clear();
//var list = (StiDataBand)report.GetComponentByName("DataBand1");
}
private void printPerson()
{
if (comboreshte2.Text == "" || dataGridViewX2.RowCount == 0)
{
return;
}
string sqlperson = "SELECT person.family + ' ' + person.name[name], person.father, person.tarikht[tavalod], " +
" person.codeMeli[meli], person.shsh, person.sadere, [P&R].nomreN[nazari],[P&R].nomreA[amali], [P&R].shGovahi[govahiname] " +
" FROM person INNER JOIN [P&R] ON person.codeMeli = [P&R].PID WHERE ([P&R].Rname = N'" + comboreshte2.Text + "')AND(person.codeMeli = N'" + textBoxX1.Text + "') ORDER BY name";
string sqlVariable = "SELECT reshte.code[codeEstandard], reshte.name[nameReshte], reshte.date[DateAzmoon], reshte.onvan[onvanReshte], reshte.dateShoroo[DateShoroo], [P&R].dateSodoor[DateSodoor] " +
"FROM reshte INNER JOIN [P&R] ON reshte.code = [P&R].RID WHERE (reshte.name = N'" + comboreshte2.Text + "')";
string str = Application.StartupPath + "\\rep\\perrep.mrt";
DataManagement.DT = DataManagement.Search(sqlperson);
try
{
Stimulsoft.Report.StiReport stikol = new Stimulsoft.Report.StiReport();
stikol.Load(str);
stikol.RegData("person", DataManagement.DT);
stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
DataManagement.DT = DataManagement.Search(sqlVariable);
DataManagement.DT.Columns.Add("number");
DataManagement.DT.Columns.Add("nameMarkaz");
DataManagement.DT.Columns.Add("nahiye");
DataManagement.DT.Columns.Add("sal");
DataManagement.DT.Columns.Add("nobat");
//DataManagement.DT.Rows[0]["number"] = dataGridViewX1.RowCount.ToString();
//DataManagement.DT.Rows[0]["nameMarkaz"] = txtNameMarkaz.Text;
//DataManagement.DT.Rows[0]["nahiye"] = txtNahiye.Text;
//DataManagement.DT.Rows[0]["sal"] = txtSal.Text;
//DataManagement.DT.Rows[0]["nobat"] = txtNobat.Text;
stikol.Load(str);
stikol.RegData("variable", DataManagement.DT);
stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
stikol.Show();
}
catch
{
FMessegeBox.FarsiMessegeBox.Show("مشکل در چاپ اطلاعات", "اخطار");
}
}
private void btnfaragir_Click(object sender, EventArgs e)
{
string sqlselectperson = "SELECT codeMeli, name, family, father, shsh, tarikht, sadere, mtavalod, sal, mah, mobile " +
" FROM person WHERE(codeMeli = N'" + dataGridViewX2.CurrentRow.Cells[3].Value.ToString() + "')";
string sqlselectreshte = "SELECT name, onvan, vamali, vnazari FROM reshte WHERE (name = N'" + comboreshte2.Text + "')";
string sqlRP = "SELECT [P&R].PID, reshte.name, reshte.onvan, reshte.vamali, reshte.vnazari " +
" FROM [P&R] INNER JOIN reshte ON [P&R].RID = reshte.code " +
" WHERE([P&R].PID = N'" + dataGridViewX2.CurrentRow.Cells[3].Value.ToString() + "') AND (reshte.name = N'" + comboreshte2.Text + "')";
try
{
string str = Application.StartupPath + "\\rep\\Sabtnam.mrt";
Stimulsoft.Report.StiReport stikol = new Stimulsoft.Report.StiReport();
Stimulsoft.Report.StiReport stitmp = new Stimulsoft.Report.StiReport();
stitmp.Load(str);
//DataSet ds = new DataSet();
DataTable dt1 = DataManagement.Search(sqlselectperson);
//ds.Tables.Add(dt1);
DataTable dt2 = DataManagement.Search(sqlRP);
//ds.Tables.Add(dt2);
//ds.Tables[0].TableName = "khate1";
//ds.Tables[1].TableName = "variable1";
stitmp.RegData("khate1", dt1);
stitmp.RegData("variable1", dt2);
//stikol.Report = stitmp;
stikol.Show(this);
//stikol.Dictionary.DataSources.Items[0].Dictionary.DataSources = ds;
//stikol.RegData("variable1", dtPerson);
//stikol.Dictionary.DataSources.Items[1].DataTable = dtPerson;
}
catch (Exception ex) { FMessegeBox.FarsiMessegeBox.Show(ex.Message); }
}
protected void btnZoneReport_Click(object sender, EventArgs e)
{
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.Load(HttpContext.Current.Server.MapPath(this.drpReportType.SelectedIndex == 1 ? "~/App_Data/Report/mrt/fc_zone-replace.mrt" : "~/App_Data/Report/mrt/fc_zone-dis.mrt"));
switch (this.drpReportType.SelectedIndex)
{
case 0:
report.RegData(ZoneDiscards());
break;
case 1:
report.RegData(ZoneReplacement());
break;
case 2:
report.RegData(ZoneDiscardeds());
break;
}
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
private void toolStripButtonDesign_Click(object sender, EventArgs e)
{
if (treeViewReports.SelectedNode.Index != -1)
{
using (Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport())
{
report.Load(ReportsPath + treeViewReports.SelectedNode.Text + ".mrt");
report.Dictionary.Databases.Clear();
report.RegData("Demo", "Demo", dataSetInvoice);
report.Compile();
report.Design(true);
}
}
else
{
MessageBox.Show("Select invoice first to proceed!");
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("RenderInThread.Master-Detail-Subdetail.mrt"))
{
report.Load(stream);
}
DataSet data = new DataSet();
data.ReadXmlSchema("..\\..\\Data\\demo.xsd");
data.ReadXml("..\\..\\Data\\demo.xml");
report.RegData(data);
report.IsRendered = false;
report.Compile();
report.CompiledReport.Rendering += new EventHandler(CompiledReport_Rendering);
report.Render(false);
}
private void buttonDesign_Click(object sender, RoutedEventArgs e)
{
if (treeViewReports.SelectedItem != null)
{
using (Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport())
{
report.Load(ReportsPath + treeViewReports.SelectedItem.ToString() + ".mrt");
report.Dictionary.Databases.Clear();
report.RegData("Demo", "Demo", dataSetInvoice);
report.Compile();
report.DesignWithWpf();
}
}
else
{
Stimulsoft.Report.Wpf.StiMessageBox.Show("Select invoice first to proceed!");
}
}
public void OnRunningConnectedAccountsrReport(Entity reportEntity, Stimulsoft.Report.StiReport report, Dictionary <string, object> parameters)
{
DataTable connectedAccountstDataTable = new DataTable("ReportTable");
var core = CoreComponent.Instance;
var connectedAccounts = core.TryGetUserSessionKeyValue("acc", "ConnectedAccounts");
EntityList connectedAccountsEntityList = (EntityList)connectedAccounts;
//var accountReviewType = parameters["AccountReviewType"];
connectedAccountstDataTable.Columns.Add("CodeAndName");
connectedAccountstDataTable.Columns.Add("AccountName");
connectedAccountstDataTable.Columns.Add("DebtorAmount", typeof(decimal));
connectedAccountstDataTable.Columns.Add("CreditorAmount", typeof(decimal));
//connectedAccountstDataTable.Columns.Add("RemainingDebtorAmount", typeof(decimal));
//connectedAccountstDataTable.Columns.Add("RemainingCreditorAmount", typeof(decimal));
foreach (var entity in connectedAccountsEntityList.Entities)
{
var row = connectedAccountstDataTable.NewRow();
row["CodeAndName"] = entity.GetFieldValue <string>("CodeAndName");
row["AccountName"] = entity.GetFieldValue <string>("AccountName");
//row["DebtorAmount"] = entity.GetFieldValue<string>("AccountCode");
//row["CreditorAmount"] = entity.GetFieldValue<string>("FarsiAccountReviewType");
row["DebtorAmount"] = entity.GetFieldValue <decimal>("DebtorAmount");
row["CreditorAmount"] = entity.GetFieldValue <decimal>("CreditorAmount");
//row["RemainingDebtorAmount"] = entity.GetFieldValue<decimal>("RemainingDebtorAmount");
//row["RemainingCreditorAmount"] = entity.GetFieldValue<decimal>("RemainingCreditorAmount");
connectedAccountstDataTable.Rows.Add(row);
}
report.Dictionary.DataSources.Clear();
report.RegData("ReportTable", connectedAccountstDataTable);
report.Dictionary.Synchronize();
StiDataBand dataBand1 = (StiDataBand)report.GetComponentByName("DataBand1");
dataBand1.DataSourceName = "ReportTable";
}
protected void Page_Load(object sender, EventArgs e)
{
MetodosPontoFrequencia.Frequencia Freq = new MetodosPontoFrequencia.Frequencia();
DataSetPontoFrequencia ds = new DataSetPontoFrequencia();
MetodosPontoFrequencia.PreencheTabela pt = new MetodosPontoFrequencia.PreencheTabela();
Freq.HorasDias(7, 688, 2014, ds, 4, 7);
Stimulsoft.Report.StiReport Report = new Stimulsoft.Report.StiReport();
//Stimulsoft.Report.Web.StiWebDesigner st1 = new Stimulsoft.Report.Web.StiWebDesigner();
Report.Load(Server.MapPath(@"~/Relatorio/RPT/rptFolhaFrequencia.mrt"));
Report.RegData("DatasetPontoFrequencia", ds);
Report.Render();
System.IO.MemoryStream memStream = new System.IO.MemoryStream();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.AddHeader("Teste.pdf", "");
Stimulsoft.Report.Export.StiPdfExportService Export = new Stimulsoft.Report.Export.StiPdfExportService();
Export.ExportPdf(Report, memStream, Stimulsoft.Report.StiPagesRange.All, 100, 100, false, false, true, true, "", "", Stimulsoft.Report.Export.StiUserAccessPrivileges.All, Stimulsoft.Report.Export.StiPdfEncryptionKeyLength.Bit40, false);
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "inline; filename=Teste.pdf");
Response.BinaryWrite(memStream.ToArray());
//Stimulsoft.Report.Web.StiReportResponse.ResponseAs(this,
//st1.Design(Report);
}
protected void SetandoRel()
{
//Report da stimulsoft
Stimulsoft.Report.StiReport Report = new Stimulsoft.Report.StiReport();
//Stimulsoft.Report.Web.StiWebDesigner st1 = new Stimulsoft.Report.Web.StiWebDesigner();
if (Convert.ToString(Request.QueryString["Rel"]) == "frmZuxa")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/EspelhoPonto.mrt"));
Report.Compile();
Report["DiasMes"] = TotalDiasMes.ToString();
Report["DiasCumpridos"] = TotalDiasCumprido.ToString();
Report["TotalHorasMes"] = TotalHoraMes.ToString();
Report["HorasCumpridas"] = HorasCumpridas.ToString();
}
Report.RegData("DatasetPontoFrequencia", ds);
Report.Render();
System.IO.MemoryStream memStream = new System.IO.MemoryStream();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.AddHeader("Pontoweb.pdf", "");
Stimulsoft.Report.Export.StiPdfExportService Export = new Stimulsoft.Report.Export.StiPdfExportService();
Export.ExportPdf(Report, memStream, Stimulsoft.Report.StiPagesRange.All, 100, 100, false, false, true, true,
"", "", Stimulsoft.Report.Export.StiUserAccessPrivileges.All, Stimulsoft.Report.Export.StiPdfEncryptionKeyLength.Bit40, false);
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "inline; filename=Pontoweb.pdf");
Response.BinaryWrite(memStream.ToArray());
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_invr336 invr336Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtIlaTb;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
List <Master> resultList = null;
try
{
invr336Model = DrMaster.ToItem <vw_invr336>();
resultList = new List <Master>();
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr336Model.ifa01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ifa_tb";
queryModel.ColumnName = "ifa01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ifa01"].DataType.Name;
queryModel.Value = invr336Model.ifa01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr336Model.ifa03))
{
queryModel = new QueryInfo();
queryModel.TableName = "ifa_tb";
queryModel.ColumnName = "ifa03";
queryModel.ColumnType = TabMaster.DtSource.Columns["ifa03"].DataType.Name;
queryModel.Value = invr336Model.ifa03;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr336Model.ifa04))
{
queryModel = new QueryInfo();
queryModel.TableName = "ifa_tb";
queryModel.ColumnName = "ifa04";
queryModel.ColumnType = TabMaster.DtSource.Columns["ifa04"].DataType.Name;
queryModel.Value = invr336Model.ifa04;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr336Model.ifa02_s))
{
sbQuerySingle.AppendLine("AND ifa02>=@ifa02_s");
sqlParmList.Add(new SqlParameter("@ifa02_s", invr336Model.ifa02_s));
}
if (!GlobalFn.varIsNull(invr336Model.ifa02_e))
{
sbQuerySingle.AppendLine("AND ifa02<=@ifa02_e");
sqlParmList.Add(new SqlParameter("@ifa02_s", invr336Model.ifa02_e));
}
if (invr336Model.conf_yn != "0")
{
if (invr336Model.conf_yn == "1")
{
sbQuerySingle.AppendLine("AND ifaconf = 'Y'");
}
if (invr336Model.conf_yn == "2")
{
sbQuerySingle.AppendLine("AND ifaconf = 'N'");
}
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"
SELECT
ifa01,ifa02,ifa03,bec02 as ifa03_c ,
ifa04,beb03 AS ifa04_c,ifa05,ifaconf,
ifb02,ifb03,ifb04,ifb05,'' as ifb05_str,ifb06,
bej03,ifb07,
ifb08,'' as ifb08_str,ifb09,ifb10,
ica03
FROM ifa_tb
LEFT JOIN ifb_tb ON ifa01=ifb01
LEFT JOIN bec_tb ON ifa03=bec01
LEFT JOIN beb_tb ON ifa04=beb01
LEFT JOIN bej_tb ON ifb06=bej01
LEFT JOIN ica_tb ON ifb03=ica01
WHERE ifa00='1'
AND ifb01 IS NOT NULL
";
dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtIlaTb.TableName = "Master";
if (dtIlaTb != null)
{
resultList.AddRange(dtIlaTb.ToList <Master>());
}
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
foreach (Master masterModel in resultList)
{
//數量處理
masterModel.ifb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ifb05); //數量
masterModel.ifb08_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ifb08); //數量
}
pReport.RegData("master", resultList);
pReport.CacheAllData = true;
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public virtual ActionResult GetReportSnapshot(string fileName, string sourceName, string sourceData)
{
if (string.IsNullOrEmpty(fileName))
{
return(Content("未能获取到打印格式信息!"));
}
DataTable dt = new DataTable();
if (sourceName == "goods")
{
dt = BLLFactory <Mst_Goods> .Instance.GetPrint(sourceData);//
}
if (sourceName == "printInStock")
{
dt = BLLFactory <PI_Head> .Instance.GetPrint(sourceData);
}
if (sourceName == "printOutstock")
{
dt = BLLFactory <SO_Head> .Instance.GetPrint(sourceData);
}
if (sourceName == "PrintMask")
{
//将object类强转list集合
List <SO_StockMakeBodyInfo> list = Session["MaskList"] as List <SO_StockMakeBodyInfo>;
//实例化dataTable
dt = new DataTable();
//给datatable添加列
dt.Columns.Add("F_GoodsName");
dt.Columns.Add("F_SellingPrice");
dt.Columns.Add("F_RealNumber");
dt.Columns.Add("F_Unit");
dt.Columns.Add("F_CargoPositionName");
dt.Columns.Add("F_WarehouseName");
//添加数据
foreach (SO_StockMakeBodyInfo item in list)
{
DataRow dr = dt.NewRow();
dr["F_GoodsName"] = item.F_GoodsName;
dr["F_SellingPrice"] = item.F_SellingPrice;
dr["F_RealNumber"] = item.F_RealNumber;
dr["F_Unit"] = item.F_Unit;
dr["F_RealNumber"] = item.F_RealNumber;
dr["F_CargoPositionName"] = item.F_CargoPositionName;
dr["F_WarehouseName"] = item.F_WarehouseName;
dt.Rows.Add(dr);
}
}
PrintModel model = new PrintModel();
model.fileName = fileName;
model.sourceName = "Source";
model.sourceData = dt;
// 创建报表对象
Stimulsoft.Report.StiReport report1 = new Stimulsoft.Report.StiReport();
//读取文件
report1.Load(Server.MapPath(string.Format("~/Content/reports/{0}.mrt", model.fileName)));
//截止读取
report1.Compile();
if (!string.IsNullOrEmpty(model.sourceName) && model.sourceData != null)
{
//注册数据源P
report1.RegData(model.sourceName, model.sourceData);
}
//返回报表
return(Stimulsoft.Report.Mvc.StiMvcViewer.GetReportSnapshotResult(report1));
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_purr500 purr500Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Purr500 != null) //他窗引用時
{
purr500Model = Vw_Purr500;
}
else
{
purr500Model = DrMaster.ToItem <vw_purr500>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(purr500Model.pha01))
{
queryModel = new QueryInfo();
queryModel.TableName = "pha_tb";
queryModel.ColumnName = "pha01";
queryModel.ColumnType = TabMaster.DtSource.Columns["pha01"].DataType.Name;
queryModel.Value = purr500Model.pha01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(purr500Model.pha03))
{
queryModel = new QueryInfo();
queryModel.TableName = "pha_tb";
queryModel.ColumnName = "pha03";
queryModel.ColumnType = TabMaster.DtSource.Columns["pha03"].DataType.Name;
queryModel.Value = purr500Model.pha03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(purr500Model.pha02_s))
{
sbQuerySingle.AppendLine("AND pha02>=@pha02_s");
sqlParmList.Add(new SqlParameter("@pha02_s", purr500Model.pha02_s));
}
if (!GlobalFn.varIsNull(purr500Model.pha02_e))
{
sbQuerySingle.AppendLine("AND pha02<=@pha02_e");
sqlParmList.Add(new SqlParameter("@pha02_e", purr500Model.pha02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT pha_tb.*,
pca02 as pha03_c,
bec02 as pha04_c,
beb02 as pha05_c,
bab02 as pha01_c,
bea03,bea04,bea05,
phb02,phb03,phb04,phb05,phb06,
phb09,phb10,phb10t,
phb16,
ica03,
bej03
FROM pha_tb
LEFT JOIN phb_tb ON pha01=phb01
LEFT JOIN pca_tb ON pha03=pca01 --廠商
LEFT JOIN bec_tb ON pha04=bec01 --員工
LEFT JOIN beb_tb ON pha05=beb01 --部門
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(pha01,1,baa06)=bab01
LEFT JOIN bea_tb ON phacomp=beacomp
LEFT JOIN ica_tb ON phb03=ica01
LEFT JOIN bej_tb ON phb03=bej01
WHERE 1=1
AND phaconf='Y'
";
//處理排序
switch (purr500Model.order_by)
{
case "1": //1.依出貨日期
sqlOrderBy = " ORDER BY pha02";
break;
case "2": //2.依客戶
sqlOrderBy = " ORDER BY pha03";
break;
}
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
//處理金額
if (!GlobalFn.varIsNull(masterModel.pha10))
{
var bekModel = BoBas.OfGetBekModel(masterModel.pha10);
if (bekModel != null)
{
//單頭
masterModel.pha13_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13);
masterModel.pha13t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13t);
masterModel.pha13g_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13g);
//單身
masterModel.phb09_str = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.phb09);
masterModel.phb10_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.phb10);
masterModel.phb10t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.phb10t);
}
}
masterModel.phb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.phb05);//數量
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (purr500Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnAjancy_Click(object sender, EventArgs e)
{
db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
var query = from j in db.Ajancies
join bl in db.BusinessLicenses on j.AjancyID equals bl.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ur in db.UsersInRoles on jp.UserRoleID equals ur.UserRoleID
join u in db.Users on ur.UserID equals u.UserID
join p in db.Persons on u.PersonID equals p.PersonID
join ct in db.Cities on j.CityID equals ct.CityID
where jp.LockOutDate == null &&
j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
bl.LockOutDate == null
orderby j.AjancyName
select new
{
Ajancy = j,
Utility = j.AjancyUtilities.Select(ju => new { ju.Utility }),
BusinessLicense = j.BusinessLicenses[0],
ct.ProvinceID,
ct.CityID,
City = ct.Name,
Manager = string.Format("{0} {1}", p.FirstName, p.LastName)
};
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
query = from q in query
where q.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue)
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
query = from q in query
where q.CityID == Public.ToByte(this.drpCity.SelectedValue)
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
query = from q in query
where q.Ajancy.AjancyID == Public.ToInt(this.drpAjancies.SelectedValue)
select q;
}
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("BusinessLicenseType", typeof(string)));
dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string)));
dtObj.Columns.Add(new DataColumn("OfficePosition", typeof(string)));
dtObj.Columns.Add(new DataColumn("OfficeLevel", typeof(string)));
dtObj.Columns.Add(new DataColumn("OfficeSpace", typeof(string)));
dtObj.Columns.Add(new DataColumn("BalconySpace", typeof(string)));
dtObj.Columns.Add(new DataColumn("BalconyHeight", typeof(string)));
dtObj.Columns.Add(new DataColumn("ParkingSpace", typeof(string)));
dtObj.Columns.Add(new DataColumn("ParkingState", typeof(string)));
dtObj.Columns.Add(new DataColumn("BusinessScope", typeof(string)));
dtObj.Columns.Add(new DataColumn("PoliceStation", typeof(string)));
dtObj.Columns.Add(new DataColumn("Mayor", typeof(string)));
dtObj.Columns.Add(new DataColumn("Address", typeof(string)));
dtObj.Columns.Add(new DataColumn("PostalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("Phone", typeof(string)));
dtObj.Columns.Add(new DataColumn("RegisteredPelak", typeof(string)));
dtObj.Columns.Add(new DataColumn("BluePelak", typeof(string)));
dtObj.Columns.Add(new DataColumn("EstateType", typeof(string)));
dtObj.Columns.Add(new DataColumn("DocumentType", typeof(string)));
dtObj.Columns.Add(new DataColumn("PlaceOwner", typeof(string)));
dtObj.Columns.Add(new DataColumn("WaterBillSerial", typeof(string)));
dtObj.Columns.Add(new DataColumn("ElectricityBillSerial", typeof(string)));
dtObj.Columns.Add(new DataColumn("GasBillSerial", typeof(string)));
dtObj.Columns.Add(new DataColumn("BusinessLicenseNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("MemberShipCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("SerialNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("ISIC", typeof(string)));
dtObj.Columns.Add(new DataColumn("CategoryCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("Water", typeof(string)));
dtObj.Columns.Add(new DataColumn("Electricity", typeof(string)));
dtObj.Columns.Add(new DataColumn("Gas", typeof(string)));
dtObj.Columns.Add(new DataColumn("TelePhone", typeof(string)));
dtObj.Columns.Add(new DataColumn("WC", typeof(string)));
dtObj.Columns.Add(new DataColumn("Fax", typeof(string)));
dtObj.Columns.Add(new DataColumn("Balcon", typeof(string)));
dtObj.Columns.Add(new DataColumn("Manager", typeof(string)));
dtObj.Columns.Add(new DataColumn("City", typeof(string)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.Ajancy.BusinessLicenseType == 0 ? "عادی" : "ویژه ایثارگران و جانبازان و شهدا";
row[1] = item.Ajancy.AjancyName;
row[2] = OfficePosition(item.Ajancy.OfficePosition.GetValueOrDefault());
row[3] = !string.IsNullOrEmpty(item.Ajancy.OfficeLevel) ? item.Ajancy.OfficeLevel : "1";
row[4] = item.Ajancy.OfficeSpace;
row[5] = !string.IsNullOrEmpty(item.Ajancy.BalconySpace) ? item.Ajancy.BalconySpace : "0";
row[6] = !string.IsNullOrEmpty(item.Ajancy.BalconyHeight) ? item.Ajancy.BalconyHeight : "0";
row[7] = item.Ajancy.ParkingSpace;
row[8] = item.Ajancy.ParkingState.GetValueOrDefault() ? "بلی" : "خیر";
row[9] = !string.IsNullOrEmpty(item.Ajancy.BusinessScope) ? item.Ajancy.BusinessScope : "...";
row[10] = !string.IsNullOrEmpty(item.Ajancy.PoliceStation) ? item.Ajancy.PoliceStation : "...";
row[11] = Mayor(item.Ajancy.Mayor.GetValueOrDefault());
row[12] = item.Ajancy.Address;
row[13] = item.Ajancy.PostalCode;
row[14] = item.Ajancy.Phone;
row[15] = item.Ajancy.RegisteredPelak;
row[16] = !string.IsNullOrEmpty(item.Ajancy.BluePelak) ? item.Ajancy.BluePelak : "...";
row[17] = item.Ajancy.EstateType.GetValueOrDefault() == 0 ? "ملکی" : "استیجاری";
row[18] = DocumentType(item.Ajancy.DocumentType.GetValueOrDefault());
row[19] = !string.IsNullOrEmpty(item.Ajancy.PlaceOwner) ? item.Ajancy.PlaceOwner : "...";
row[20] = item.Ajancy.WaterBillSerial;
row[21] = item.Ajancy.ElectricityBillSerial;
row[22] = item.Ajancy.GasBillSerial;
row[23] = item.BusinessLicense.BusinessLicenseNo;
row[24] = item.BusinessLicense.MemberShipCode;
row[25] = item.BusinessLicense.SerialNo;
row[26] = item.BusinessLicense.ISIC;
row[27] = item.BusinessLicense.CategoryCode;
row[28] = "ندارد";
row[29] = "ندارد";
row[30] = "ندارد";
row[31] = "ندارد";
row[32] = "ندارد";
row[33] = "ندارد";
foreach (var util in item.Utility)
{
switch (util.Utility)
{
case 0:
row[28] = "دارد";
break;
case 1:
row[29] = "دارد";
break;
case 2:
row[30] = "دارد";
break;
case 3:
row[31] = "دارد";
break;
case 4:
row[32] = "دارد";
break;
case 5:
row[33] = "دارد";
break;
}
}
if (!string.IsNullOrEmpty(item.Ajancy.BalconySpace) && item.Ajancy.BalconySpace.ToString() != "0")
{
row[34] = "بلی";
}
else
{
row[34] = "خیر";
}
row[35] = item.Manager;
row[36] = item.City;
dtObj.Rows.Add(row);
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/ajancy_all.mrt"));
report.RegData(dtObj);
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
protected void btnExcel_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
var query = from s in db.Schools
join slv in db.SchoolLevels on s.SchoolID equals slv.SchoolID
join ssl in db.SchoolSubLevels on slv.SchoolLevelID equals ssl.SchoolLevelID
join sc in db.SchoolClothes on ssl.SchoolSubLevelID equals sc.SchoolSubLevelID
join st in db.StudentClothes on sc.SchoolClotheID equals st.SchoolClotheID
join cc in db.CycleClothes on sc.CycleClotheID equals cc.CycleClotheID
join stf in db.REP_Stuffs on cc.StuffID equals stf.StuffID
join a in db.Areas on s.AreaCode equals a.AreaCode
join lv in db.Levels on slv.LevelID equals lv.LevelID
where a.ProvinceID == 71 && cc.CycleID == Public.ToInt(this.drpCycle.SelectedValue) &&
slv.LockOutDate == null && cc.CycleClotheID == Public.ToInt(this.drpStuffs.SelectedValue)
group ssl by new { s.SchoolCode, s.SchoolName, lv.LevelName, a.AreaCode, a.AreaName, st.ClotheCount, stf.StuffName, cc.CycleClotheID } into grp
orderby grp.Key.SchoolCode
select new
{
grp.Key.SchoolCode,
grp.Key.SchoolName,
grp.Key.LevelName,
grp.Key.AreaCode,
grp.Key.AreaName,
grp.Key.StuffName,
ClotheCount = grp.Count()
};
if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
{
query = from q in query
where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
select q;
}
if (!string.IsNullOrEmpty(this.txtSchoolCode.Text))
{
query = from q in query
where q.SchoolCode == Public.ToInt(this.txtSchoolCode.Text)
select q;
}
var result = from q in query
group q by new { q.SchoolCode, q.SchoolName, q.AreaName, q.StuffName } into grp
select new
{
grp.Key.SchoolCode,
grp.Key.SchoolName,
grp.Key.AreaName,
grp.Key.StuffName,
ClotheCount = grp.Sum(s => s.ClotheCount)
};
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("SchoolCode", typeof(int)));
dtObj.Columns.Add(new DataColumn("SchoolName", typeof(string)));
dtObj.Columns.Add(new DataColumn("AreaName", typeof(string)));
dtObj.Columns.Add(new DataColumn("GoodName", typeof(string)));
dtObj.Columns.Add(new DataColumn("ClotheCount", typeof(short)));
dtObj.Columns.Add(new DataColumn("LevelName", typeof(string)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.SchoolCode;
row[1] = item.SchoolName;
row[2] = item.AreaName;
row[3] = item.StuffName;
row[4] = item.ClotheCount;
row[5] = item.LevelName;
dtObj.Rows.Add(row);
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData(dtObj);
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch_clothe.mrt"));
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_invr102 invr102Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
object icp03;
string selectSql = "";
List <SqlParameter> sqlParmList;
try
{
//if (Vw_Invr301 != null) //他窗引用時
// invr301Model = Vw_Invr301;
//else
// invr301Model = DrMaster.ToItem<vw_invr301>();
queryInfoList = new List <QueryInfo>();
#region range 處理
invr102Model = DrMaster.ToItem <vw_invr102>();
if (!GlobalFn.varIsNull(invr102Model.ica01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ica_tb";
queryModel.ColumnName = "ica01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ica01"].DataType.Name;
queryModel.Value = invr102Model.ica01;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
strWhere = strQueryRange;
//var strSecurity = WfGetSecurityString(); //取得權限字串
//if (!GlobalFn.varIsNull(strSecurity))
// strWhere += strSecurity;
//取得單頭
//sqlBody = @"SELECT V.ica01,v.ica02,v.ica11,v.icc05_tot
sqlBody = @"SELECT V.*
FROM
(
SELECT a.*,
(
SELECT SUM(icc05) as icc05_tot
FROM icc_tb p
WHERE p.icc01=a.ica01
) AS icc05_tot
FROM ica_tb a
WHERE
LEN(a.ica01)=11
AND a.icaconf='Y'
AND ISNULL(a.ica30,'') <>'Y'
) V
WHERE
V.icc05_tot>0
order by 2,1
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
selectSql = @"
SELECT TOP 1 icp03
FROM icp_tb
WHERE icp01=@icp01
ORDER BY ISNULL(icp06,'N') DESC,
icp05
";
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@icp01", masterModel.ica01));
icp03 = BoInv.OfGetFieldValue(selectSql, sqlParmList.ToArray());
if (icp03 != null && icp03 != DBNull.Value)
{
masterModel.icp03 = Image.FromStream(new MemoryStream((byte[])icp03));;
}
masterModel.ica03 = masterModel.ica02;
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
//StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
//if (invr101Model.jump_yn.ToUpper() == "Y")
//{
// footerBand1.NewPageAfter = true;
// footerBand1.ResetPageNumber = true;
//}
//else
//{
// footerBand1.NewPageAfter = false;
// footerBand1.ResetPageNumber = false;
//}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
private void printPerson()
{
if (comboreshte1.Text == "" || dataGridViewX1.RowCount == 0 || comboonvan1.Text == "")
{
return;
}
string sqlperson = "SELECT person.family + ' ' + person.name[name], person.father, person.tarikht[tavalod], " +
" person.codeMeli[meli], person.shsh, person.sadere, [P&R].nomreN[nazari],[P&R].nomreA[amali], [P&R].shGovahi[govahiname] " +
" FROM person INNER JOIN [P&R] ON person.codeMeli = [P&R].PID WHERE ([P&R].Rname = N'" + comboreshte1.Text + "'){0}{1}{2} ORDER BY name";
string.Format(sqlperson, (comboonvan1.Text == "" ? "" : "AND([P&R].Ronvan = N'" + comboreshte1.Text + "')"), (combosal1.Text == "" ? "" : "AND(person.sal = N'" + combosal1.Text + "')")
, (combomah1.Text == "" ? "" : "AND(person.mah = N'" + combomah1.Text + "')"));
string sqlVariable = "SELECT reshte.code[codeEstandard], reshte.name[nameReshte], reshte.date[DateAzmoon], reshte.onvan[onvanReshte], reshte.dateShoroo[DateShoroo], [P&R].dateSodoor[DateSodoor] " +
"FROM reshte INNER JOIN [P&R] ON reshte.code = [P&R].RID WHERE (reshte.name = N'" + comboreshte1.Text + "')AND(reshte.onvan = N'" + comboonvan1.Text + "')";
string str = Application.StartupPath + "\\rep\\perrep.mrt";
DataManagement.DT = DataManagement.Search(sqlperson);
try
{
for (int i = 0; i < DataManagement.DT.Rows.Count; i++)
{
bool x = false;
int j = 0;
for (; j < dataGridViewX1.SelectedRows.Count; j++)
{
if (dataGridViewX1.SelectedRows[j].Cells[0].Value.ToString()
== DataManagement.DT.Rows[i][0].ToString())
{
x = true;
break;
}
}
if (x)
{
continue;
}
DataManagement.DT.Rows.RemoveAt(i);
i--;
}
Stimulsoft.Report.StiReport stikol = new Stimulsoft.Report.StiReport();
stikol.Load(str);
stikol.RegData("person", DataManagement.DT);
stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
DataManagement.DT = DataManagement.Search(sqlVariable);
DataManagement.DT.Columns.Add("number");
DataManagement.DT.Columns.Add("nameMarkaz");
DataManagement.DT.Columns.Add("nahiye");
DataManagement.DT.Columns.Add("sal");
DataManagement.DT.Columns.Add("nobat");
DataManagement.DT.Rows[0]["number"] = dataGridViewX1.RowCount.ToString();
DataManagement.DT.Rows[0]["nameMarkaz"] = txtNameMarkaz.Text;
DataManagement.DT.Rows[0]["nahiye"] = txtNahiye.Text;
DataManagement.DT.Rows[0]["sal"] = txtSal.Text;
DataManagement.DT.Rows[0]["nobat"] = txtNobat.Text;
stikol.Load(str);
stikol.RegData("variable", DataManagement.DT);
stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
stikol.Show();
}
catch
{
FMessegeBox.FarsiMessegeBox.Show("مشکل در چاپ اطلاعات", "اخطار");
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_invr522 invr522Model;
string sqlBody = "";
DataTable dtIlaTb;
List<QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere, strOrderBy;
StringBuilder sbQuerySingle = null;
List<SqlParameter> sqlParmList;
List<Master> resultList = null;
try
{
invr522Model = DrMaster.ToItem<vw_invr522>();
resultList = new List<Master>();
queryInfoList = new List<QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr522Model.ipa01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipa01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipa01"].DataType.Name;
queryModel.Value = invr522Model.ipa01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr522Model.ipacreu))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipacreu";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreu"].DataType.Name;
queryModel.Value = invr522Model.ipacreu;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr522Model.ipacreg))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipacreg";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreg"].DataType.Name;
queryModel.Value = invr522Model.ipacreg;
queryInfoList.Add(queryModel);
}
sqlParmList = new List<SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr522Model.ipa03_s))
{
sbQuerySingle.AppendLine("AND ipa03>=@ipa03_s");
sqlParmList.Add(new SqlParameter("@ila02_s", invr522Model.ipa03_s));
}
if (!GlobalFn.varIsNull(invr522Model.ipa03_e))
{
sbQuerySingle.AppendLine("AND ipa03<=@ipa03_e");
sqlParmList.Add(new SqlParameter("@ipa03_e", invr522Model.ipa03_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
strWhere += strSecurity;
//取得單頭
sqlBody = @"
SELECT
ipa01,ipb02,ipb03,ipb04,ipb05,
ipb06,ipb07,ipb30,ipb40,ipb50,
ica02,ica03,
bej03,
CONVERT(DECIMAL,0) AS qty1,
CONVERT(DECIMAL,0) AS qty2,
CONVERT(DECIMAL,0) AS dif_qty,
CONVERT(NVARCHAR(20),0) AS qty1_str,
CONVERT(NVARCHAR(20),0) AS qty2_str,
CONVERT(NVARCHAR(20),0) AS dif_qty_str
FROM ipa_tb
INNER JOIN ipb_tb ON ipa01=ipb01
LEFT JOIN ica_tb ON ipb03=ica01
LEFT JOIN bej_tb ON ipb07=bej01
WHERE
ipa05='Y'
";
strOrderBy = " ORDER BY ipa01,ipb02";
dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, strOrderBy), sqlParmList.ToArray());
dtIlaTb.TableName = "Master";
if (dtIlaTb != null)
{
resultList.AddRange(dtIlaTb.ToList<Master>());
}
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return false;
}
foreach (Master masterModel in resultList)
{
switch (invr522Model.type1)
{
case "1":
masterModel.qty1 = masterModel.ipb30;
break;
case "2":
masterModel.qty1 = masterModel.ipb40;
break;
case "3":
masterModel.qty1 = masterModel.ipb50;
break;
case "4":
masterModel.qty1 = masterModel.ipb06;
break;
}
switch (invr522Model.type2)
{
case "1":
masterModel.qty2 = masterModel.ipb30;
break;
case "2":
masterModel.qty2 = masterModel.ipb40;
break;
case "3":
masterModel.qty2 = masterModel.ipb50;
break;
case "4":
masterModel.qty2 = masterModel.ipb06;
break;
}
masterModel.dif_qty = masterModel.qty2 - masterModel.qty1;
masterModel.qty1_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty1);
masterModel.qty2_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty2);
masterModel.dif_qty_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.dif_qty);
}
if (invr522Model.dif_yn == "Y")//只顯示差異
{
if (resultList.Where(x => x.dif_qty != 0).Count() == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return false;
}
pReport.RegData("master", resultList.Where(x => x.dif_qty != 0));
}
else
{
pReport.RegData("master", resultList);
}
pReport.CacheAllData = true;
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
pReport.Compile();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
var query = from j in db.Ajancies
join bl in db.BusinessLicenses on j.AjancyID equals bl.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ur in db.UsersInRoles on jp.UserRoleID equals ur.UserRoleID
join u in db.Users on ur.UserID equals u.UserID
join p in db.Persons on u.PersonID equals p.PersonID
join ct in db.Cities on j.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
where jp.LockOutDate == null &&
j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) && bl.LockOutDate == null
orderby pv.Name, ct.Name, j.AjancyName
select new
{
ct.ProvinceID,
ct.CityID,
Province = pv.Name,
City = ct.Name,
j.AjancyName,
j.Phone,
j.PostalCode,
j.Address,
bl.BusinessLicenseNo,
j.BusinessLicenseType,
p.NationalCode,
p.FirstName,
p.LastName,
p.Mobile
};
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
query = from q in query
where q.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue)
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
query = from q in query
where q.CityID == Public.ToShort(this.drpCity.SelectedValue)
select q;
}
if (!string.IsNullOrEmpty(this.txtAjancyName.Text))
{
query = from q in query
where q.AjancyName.Contains(this.txtAjancyName.Text.Trim())
select q;
}
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("FirstName", typeof(string)));
dtObj.Columns.Add(new DataColumn("LastName", typeof(string)));
dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("Mobile", typeof(string)));
dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string)));
dtObj.Columns.Add(new DataColumn("BusinessLicenseNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("BusinessLicenseType", typeof(string)));
dtObj.Columns.Add(new DataColumn("Address", typeof(string)));
dtObj.Columns.Add(new DataColumn("PostalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("Phone", typeof(string)));
dtObj.Columns.Add(new DataColumn("Province", typeof(string)));
dtObj.Columns.Add(new DataColumn("City", typeof(string)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.FirstName;
row[1] = item.LastName;
row[2] = item.NationalCode;
row[3] = item.Mobile;
row[4] = item.AjancyName;
row[5] = item.BusinessLicenseNo;
row[6] = item.BusinessLicenseType == 0 ? "عادی" : "ویژه ایثارگران و جانبازان و شهدا";
row[7] = item.Address;
row[8] = item.PostalCode;
row[9] = item.Phone;
row[10] = item.Province;
row[11] = item.City;
dtObj.Rows.Add(row);
}
db.Dispose();
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData(dtObj);
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/ajancy_list.mrt"));
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_stpr410 stpr410Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtSgaTb, dtShaTb;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
List <MasterA> resultList = null;
try
{
stpr410Model = DrMaster.ToItem <vw_stpr410>();
resultList = new List <MasterA>();
#region 處理銷售
if (stpr410Model.sale_type == "0" || stpr410Model.sale_type == "1")
{
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(stpr410Model.sga03))
{
queryModel = new QueryInfo();
queryModel.TableName = "sga_tb";
queryModel.ColumnName = "sga03";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga03"].DataType.Name;
queryModel.Value = stpr410Model.sga03;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(stpr410Model.sga04))
{
queryModel = new QueryInfo();
queryModel.TableName = "sga_tb";
queryModel.ColumnName = "sga04";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga04"].DataType.Name;
queryModel.Value = stpr410Model.sga04;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(stpr410Model.sga05))
{
queryModel = new QueryInfo();
queryModel.TableName = "sga_tb";
queryModel.ColumnName = "sga05";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga05"].DataType.Name;
queryModel.Value = stpr410Model.sga05;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(stpr410Model.sga02_s))
{
sbQuerySingle.AppendLine("AND sga02>=@sga02_s");
sqlParmList.Add(new SqlParameter("@sga02_s", stpr410Model.sga02_s));
}
if (!GlobalFn.varIsNull(stpr410Model.sga02_e))
{
sbQuerySingle.AppendLine("AND sga02<=@sga02_e");
sqlParmList.Add(new SqlParameter("@sga02_e", stpr410Model.sga02_e));
}
if (stpr410Model.confirm_type == "1")
{
sbQuerySingle.AppendLine("AND sgaconf ='Y'");
}
else if (stpr410Model.confirm_type == "2")
{
sbQuerySingle.AppendLine("AND ISNULL(sgaconf,'') <>'Y'");
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"
SELECT
'銷貨' AS sale_type,
sga01,sga02,sga03,sca03 as sga03_c ,
sgb02,sga04,bec02 AS sga04_c,sga05,beb03 AS sga05_c,
sga10,sga21,
sgb03,sgb04,sgb05,'' as sgb05_str,sgb06,
bej03,
0.0 as price,'' as price_string,
sgb10,'' as sgb10_string,
sgb10t,'' as sgb10t_string,
sgb16
FROM sga_tb
LEFT JOIN sgb_tb ON sga01=sgb01
LEFT JOIN sca_tb ON sga03=sca01
LEFT JOIN bec_tb ON sga04=bec01
LEFT JOIN beb_tb ON sga05=beb01
LEFT JOIN bej_tb ON sgb06=bej01
WHERE sgb01 IS NOT NULL
";
dtSgaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtSgaTb.TableName = "Master";
if (dtSgaTb != null)
{
resultList.AddRange(dtSgaTb.ToList <YR.ERP.DAL.YRModel.Reports.Stp.Stpr410.MasterA>());
}
}
#endregion
#region 處理退貨/折讓
if (stpr410Model.sale_type == "0" || stpr410Model.sale_type == "2")
{
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(stpr410Model.sga03))
{
queryModel = new QueryInfo();
queryModel.TableName = "sha_tb";
queryModel.ColumnName = "sha03";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga03"].DataType.Name;
queryModel.Value = stpr410Model.sga03;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(stpr410Model.sga04))
{
queryModel = new QueryInfo();
queryModel.TableName = "sha_tb";
queryModel.ColumnName = "sha04";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga04"].DataType.Name;
queryModel.Value = stpr410Model.sga04;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(stpr410Model.sga05))
{
queryModel = new QueryInfo();
queryModel.TableName = "sha_tb";
queryModel.ColumnName = "sha05";
queryModel.ColumnType = TabMaster.DtSource.Columns["sga05"].DataType.Name;
queryModel.Value = stpr410Model.sga05;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(stpr410Model.sga02_s))
{
sbQuerySingle.AppendLine("AND sha02>=@sha02_s");
sqlParmList.Add(new SqlParameter("@sha02_s", stpr410Model.sga02_s));
}
if (!GlobalFn.varIsNull(stpr410Model.sga02_e))
{
sbQuerySingle.AppendLine("AND sha02<=@sha02_e");
sqlParmList.Add(new SqlParameter("@sha02_e", stpr410Model.sga02_e));
}
if (stpr410Model.confirm_type == "1")
{
sbQuerySingle.AppendLine("AND shaconf ='Y'");
}
else if (stpr410Model.confirm_type == "2")
{
sbQuerySingle.AppendLine("AND ISNULL(shaconf,'') <>'Y'");
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"
SELECT
CASE WHEN shb17='1' THEN '銷退' ELSE '折讓' END AS sale_type,
sha01 AS sga01,sha02 AS sga02,sha03 AS sga03,sca03 as sga03_c ,
shb02 AS sgb02,sha04 AS sga04,bec02 AS sga04_c,sha05 AS sga05,beb03 AS sga05_c,
sha10 AS sga10,shb19 AS sga21,
shb03 AS sgb03,shb04 AS sgb04,shb05 AS sgb05,'' as sgb05_str,shb06 AS sgb06,
bej03,
0.0 as price,'' as price_string,
-1*shb10 AS sgb10,'' as sgb10_string,
-1*shb10t AS sgb10t,'' as sgb10t_string,
shb16 AS sgb16
FROM sha_tb
LEFT JOIN shb_tb ON sha01=shb01
LEFT JOIN sca_tb ON sha03=sca01
LEFT JOIN bec_tb ON sha04=bec01
LEFT JOIN beb_tb ON sha05=beb01
LEFT JOIN bej_tb ON shb06=bej01
WHERE shb01 IS NOT NULL
";
dtSgaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtSgaTb.TableName = "Master";
if (dtSgaTb != null)
{
resultList.AddRange(dtSgaTb.ToList <YR.ERP.DAL.YRModel.Reports.Stp.Stpr410.MasterA>());
}
}
#endregion
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
foreach (MasterA masterModel in resultList)
{
var bekModel = BoBas.OfGetBekModel(masterModel.sga10);
//處理單價
if (masterModel.sgb05 > 0)
{
masterModel.price = Math.Round((masterModel.sgb10 / masterModel.sgb05), Convert.ToInt16(bekModel.bek03), MidpointRounding.AwayFromZero);
}
else
{
masterModel.price = masterModel.sgb10;
}
masterModel.price_string = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.price);
masterModel.sgb10_string = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.sgb10);
masterModel.sgb10t_string = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.sgb10t);
//數量處理
masterModel.sgb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.sgb05);//數量
}
pReport.RegData("master", resultList);
pReport.CacheAllData = true;
//處理排序--這裡利用group來達到
StiGroupHeaderBand stiGroupHeaderBand1 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand1"];
StiDataBand StiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
switch (stpr410Model.order_by)
{
case "1": //1.依出庫日期
stiGroupHeaderBand1.Condition.Value = "{Master.sga02}";
StiDataBand1.Sort = new string[6] {
"ASC",
"sga02",
"ASC",
"sga01",
"ASC",
"sgb02",
};
break;
case "2": //2.依客戶
stiGroupHeaderBand1.Condition.Value = "{Master.sga03}";
StiDataBand1.Sort = new string[6] {
"ASC",
"sga03",
"ASC",
"sga01",
"ASC",
"sgb02",
};
break;
case "3": //3.依部門
stiGroupHeaderBand1.Condition.Value = "{Master.sga05}";
StiDataBand1.Sort = new string[6] {
"ASC",
"sga05",
"ASC",
"sga01",
"ASC",
"sgb02",
};
break;
case "4": //4.依業務
stiGroupHeaderBand1.Condition.Value = "{Master.sga04}";
StiDataBand1.Sort = new string[6] {
"ASC",
"sga04",
"ASC",
"sga01",
"ASC",
"sgb02",
};
break;
}
//處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (stpr410Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void SetandoRel()
{
//Report da stimulsoft
Stimulsoft.Report.StiReport Report = new Stimulsoft.Report.StiReport();
//Stimulsoft.Report.Web.StiWebDesigner st1 = new Stimulsoft.Report.Web.StiWebDesigner();
if (Convert.ToString(Request.QueryString["Rel"]) == "frmZurel")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/folhafrequencia.mrt"));
Report.Compile();
Report["DiasMes"] = TotalDiasMes.ToString();
Report["DiasCumpridos"] = TotalDiasCumprido.ToString();
Report["TotalHorasMes"] = TotalHoraMes.ToString();
Report["HorasCumpridas"] = HorasCumpridas;
Report["HorasRealizadas"] = HorasRealizadas;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmZuxa")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/EspelhoPonto.mrt"));
Report.Compile();
Report["DiasMes"] = TotalDiasMes.ToString();
Report["DiasCumpridos"] = TotalDiasCumprido.ToString();
Report["TotalHorasMes"] = TotalHoraMes.ToString();
Report["HorasCumpridas"] = HorasCumpridas.ToString();
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmsitu")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/situacaousuario.mrt"));
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmbco")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/totalhorasdiarias.mrt"));
Report.Compile();
//Report["Teste"] = 932;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmbcoHoraMes")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/bancoHoraMes.mrt"));
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmRelacDia")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/relacaopontoDia.mrt"));
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmLocalRegistro")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/LocalRegistro.mrt"));
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmMotivoFalta")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/RelacaoMotivoFalta.mrt"));
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmfca")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/FichaCadastral.mrt"));
Report.Compile();
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmfi")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/FichaCadastral.mrt"));
Report.Compile();
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmdesc")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/RelRelacaoRegistroPeriodo.mrt"));
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmfaltaInjust")
{
if (RelUtilizado == 0)
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/RelDataInjustificada.mrt"));
}
else
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/RelRegistroAusente.mrt"));
}
//o Outro Aqui
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmAuditRel")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/RelatorioAuditoriaJustificativa.mrt"));
Report.Compile();
Report["DTInicio"] = INICIO;
Report["DTFim"] = FIM;
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmLogMaqRel")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/AuditoriaGeralDasConexoes.mrt"));
Report.Compile();
}
else if (Convert.ToString(Request.QueryString["Rel"]) == "frmInfoMaqRel")
{
Report.Load(Server.MapPath(@"~/Relatorio/STF/InformacoesMaquinasColetorLocal.mrt"));
Report.Compile();
}
Report.RegData("DatasetPontoFrequencia", ds);
Report.Render();
System.IO.MemoryStream memStream = new System.IO.MemoryStream();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.AddHeader("Pontoweb.pdf", "");
Stimulsoft.Report.Export.StiPdfExportService Export = new Stimulsoft.Report.Export.StiPdfExportService();
Export.ExportPdf(Report, memStream, Stimulsoft.Report.StiPagesRange.All, 100, 100, false, false, true, true,
"", "", Stimulsoft.Report.Export.StiUserAccessPrivileges.All, Stimulsoft.Report.Export.StiPdfEncryptionKeyLength.Bit40, false);
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "inline; filename=Pontoweb.pdf");
Response.BinaryWrite(memStream.ToArray());
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_stpr500 stpr500Model;
StringBuilder sbSql = null;
DataTable dtSeaTb, dtSebTb;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Stpr500 != null) //他窗引用時
{
stpr500Model = Vw_Stpr500;
}
else
{
stpr500Model = DrMaster.ToItem <vw_stpr500>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(stpr500Model.sha01))
{
queryModel = new QueryInfo();
queryModel.TableName = "sha_tb";
queryModel.ColumnName = "sha01";
queryModel.ColumnType = TabMaster.DtSource.Columns["sha01"].DataType.Name;
queryModel.Value = stpr500Model.sha01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(stpr500Model.sha03))
{
queryModel = new QueryInfo();
queryModel.TableName = "sha_tb";
queryModel.ColumnName = "sha03";
queryModel.ColumnType = TabMaster.DtSource.Columns["sha03"].DataType.Name;
queryModel.Value = stpr500Model.sha03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(stpr500Model.sha02_s))
{
sbQuerySingle.AppendLine("AND sha02>=@sha02_s");
sqlParmList.Add(new SqlParameter("@sha02_s", stpr500Model.sha02_s));
}
if (!GlobalFn.varIsNull(stpr500Model.sha02_e))
{
sbQuerySingle.AppendLine("AND sha02<=@sha02_e");
sqlParmList.Add(new SqlParameter("@sha02_e", stpr500Model.sha02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT sha_tb.*,");
sbSql.AppendLine(" sca03 AS sha03_c,");
sbSql.AppendLine(" bec02 AS sha04_c,");
sbSql.AppendLine(" beb02 AS sha05_c,");
sbSql.AppendLine(" bab02 AS sha01_c,");
sbSql.AppendLine(" bea04,");
sbSql.AppendLine(" bea05,");
sbSql.AppendLine(" bea06");
sbSql.AppendLine("FROM sha_tb");
sbSql.AppendLine(" LEFT JOIN sca_tb ON sha03=sca01");
sbSql.AppendLine(" LEFT JOIN bec_tb ON sha04=bec01");
sbSql.AppendLine(" LEFT JOIN beb_tb ON sha05=beb01");
sbSql.AppendLine(" LEFT JOIN baa_tb ON 1=1");
sbSql.AppendLine(" LEFT JOIN bab_tb ON substring(sha01,1,baa06)=bab01");
sbSql.AppendLine(" LEFT JOIN bea_tb ON beacomp=shacomp");
sbSql.AppendLine("WHERE 1=1");
sbSql.AppendLine(" AND shaconf='Y'");
dtSeaTb = BoMaster.OfGetDataTable(string.Concat(sbSql.ToString(), strWhere), sqlParmList.ToArray());
dtSeaTb.TableName = "Master";
if (dtSeaTb == null || dtSeaTb.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
//取得明細
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM shb_tb");
sbSql.AppendLine("WHERE EXISTS(");
sbSql.AppendLine(" SELECT 1 FROM sha_tb");
sbSql.AppendLine(" WHERE sha01=shb01");
sbSql.AppendLine(strWhere);
sbSql.AppendLine(")");
dtSebTb = BoMaster.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray());
dtSebTb.TableName = "Detail";
pReport.RegData(dtSeaTb);
pReport.RegData(dtSebTb);
pReport.CacheAllData = true;
//處理排序
StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
switch (stpr500Model.order_by)
{
case "1": //1.依出貨日期
stiDataBand1.Sort = new string[] { "ASC", "sha02" };
break;
case "2": //2.依客戶
stiDataBand1.Sort = new string[] { "ASC", "sha03" };
break;
}
//處理跳頁
StiFooterBand footerBand1 = (StiFooterBand)pReport.GetComponents()["FooterBand1"];
if (stpr500Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
//pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
var query = from s in db.Schools
join slv in db.SchoolLevels on s.SchoolID equals slv.SchoolID
join lv in db.Levels on slv.LevelID equals lv.LevelID
join a in db.Areas on s.AreaCode equals a.AreaCode
where slv.LockOutDate == null
orderby a.AreaCode, s.SchoolCode
select
new
{
s.SchoolID,
s.SchoolCode,
s.SchoolName,
s.Gender,
s.SchoolKindID,
s.EmployeesCount_Changable,
s.EmployeesCount_Fixed,
lv.LevelID,
lv.LevelName,
a.AreaName,
a.AreaCode,
GirlsCount = GetGirlsCount(s),
BoysCount = GetBoysCount(s)
};
if (!string.IsNullOrEmpty(this.txtSchoolCode_From.Text) && string.IsNullOrEmpty(this.txtSchoolCode_To.Text))
{
query = from q in query
where q.SchoolCode == Public.ToInt(this.txtSchoolCode_From.Text)
select q;
}
else if (!string.IsNullOrEmpty(this.txtSchoolCode_From.Text) && !string.IsNullOrEmpty(this.txtSchoolCode_To.Text))
{
query = from q in query
where q.SchoolCode >= Public.ToInt(this.txtSchoolCode_From.Text) && q.SchoolCode <= Public.ToInt(this.txtSchoolCode_To.Text)
select q;
}
if (!string.IsNullOrEmpty(this.txtSchoolName.Text))
{
query = from q in query
where q.SchoolName.Contains(this.txtSchoolName.Text)
select q;
}
if (this.drpSchoolLevel.SelectedIndex > 0)
{
query = from q in query
where q.LevelID == Public.ToShort(this.drpSchoolLevel.SelectedValue)
select q;
}
if (this.drpSchoolKind.SelectedIndex > 0)
{
query = from q in query
where q.SchoolKindID == Public.ToShort(this.drpSchoolKind.SelectedValue)
select q;
}
if (this.drpGender.SelectedIndex < 3)
{
query = from q in query
where q.Gender == this.drpGender.SelectedIndex
select q;
}
if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
{
query = from q in query
where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
select q;
}
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("SchoolCode", typeof(int)));
dtObj.Columns.Add(new DataColumn("SchoolName", typeof(string)));
dtObj.Columns.Add(new DataColumn("LevelName", typeof(string)));
dtObj.Columns.Add(new DataColumn("AreaName", typeof(string)));
dtObj.Columns.Add(new DataColumn("BoysCount", typeof(short)));
dtObj.Columns.Add(new DataColumn("GirlsCount", typeof(short)));
dtObj.Columns.Add(new DataColumn("StudentsCount", typeof(short)));
dtObj.Columns.Add(new DataColumn("EmployeesCount_Fixed", typeof(byte)));
dtObj.Columns.Add(new DataColumn("EmployeesCount_Changable", typeof(byte)));
dtObj.Columns.Add(new DataColumn("Sum", typeof(short)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.SchoolCode;
row[1] = item.SchoolName;
row[2] = item.LevelName;
row[3] = item.AreaName;
row[4] = item.BoysCount;
row[5] = item.GirlsCount;
row[6] = item.BoysCount + item.GirlsCount;
row[7] = item.EmployeesCount_Fixed;
row[8] = item.EmployeesCount_Changable;
row[9] = item.BoysCount + item.GirlsCount + item.EmployeesCount_Fixed + item.EmployeesCount_Changable;
dtObj.Rows.Add(row);
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData(dtObj);
report.Load(System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch.mrt"));
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
byte ajancyType = Public.ToByte(this.drpAjancyType.SelectedValue);
db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
var drivers = (from p in db.Persons
join u in db.Users on p.PersonID equals u.PersonID
join dc in db.DriverCertifications on p.PersonID equals dc.PersonID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty()
from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty()
join c in db.Cars on cpn.CarID equals c.CarID
join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID
join fc in db.FuelCards on c.CarID equals fc.CarID
join j in db.Ajancies on jd.AjancyID equals j.AjancyID
join ct in db.Cities on j.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
where j.AjancyType == ajancyType && !(from fcs in db.FuelCardSubstitutions
join fc2 in db.FuelCards on fcs.AjancyTypeFuelCardID equals fc2.FuelCardID
join c2 in db.Cars on fc2.CarID equals c2.CarID
join cpn2 in db.CarPlateNumbers on c2.CarID equals cpn2.CarID
join dcc2 in db.DriverCertificationCars on cpn2.CarPlateNumberID equals dcc2.CarPlateNumberID
join dc2 in db.DriverCertifications on dcc2.DriverCertificationID equals dc2.DriverCertificationID
join jd2 in db.AjancyDrivers on dcc2.DriverCertificationCarID equals jd2.DriverCertificationCarID
join j2 in db.Ajancies on jd2.AjancyID equals j2.AjancyID
where fcs.PersonalTypeFuelCardID != null && j2.AjancyType == ajancyType &&
(c2.ChassisNo == null && c2.EngineNo == null) &&
fcs.SubmitDate < new DateTime(2013, 9, 21) &&
((fc2.DiscardDate == null && dcc2.LockOutDate == null && jd2.LockOutDate == null))
select dc2.PersonID).Contains(p.PersonID)
select new
{
OWNationalCode = owp.NationalCode,
OWFirstName = owp.FirstName,
OWLastName = owp.LastName,
p.PersonID,
p.FirstName,
p.LastName,
p.Father,
p.NationalCode,
p.BirthCertificateNo,
p.BirthCertificatePlace,
p.BirthDate,
p.Marriage,
p.BirthPlace,
p.Mobile,
p.Phone,
p.PostalCode,
p.Address,
p.Gender,
p.SubmitDate,
j.AjancyID,
j.AjancyName,
u.CityID,
u.ProvinceID,
Province = pv.Name,
City = ct.Name,
dc.DriverCertificationNo,
c.CarTypeID,
c.FuelType,
c.Model,
c.EngineNo,
c.ChassisNo,
crt.TypeName,
c.VIN,
ZCityID = (short?)zpn.CityID,
ZNumber = zpn.Number,
pln.TwoDigits,
pln.ThreeDigits,
pln.Alphabet,
pln.RegionIdentifier,
fc.CardType,
fc.PAN
}).Distinct();
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
byte provinceId = Public.ToByte(this.drpProvince.SelectedValue);
drivers = from q in drivers
where q.ProvinceID == provinceId
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
short cityId = Public.ToShort(this.drpCity.SelectedValue);
drivers = from q in drivers
where q.CityID == cityId
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue);
drivers = from q in drivers
where q.AjancyID == ajancyId
select q;
}
if (this.drpDriverCertification.SelectedIndex == 1)
{
drivers = from q in drivers
where q.DriverCertificationNo != null
select q;
}
else if (this.drpDriverCertification.SelectedIndex == 2)
{
drivers = from q in drivers
where q.DriverCertificationNo == null
select q;
}
if (this.drpCarType.SelectedIndex > 0)
{
short carTypeId = Public.ToShort(this.drpCarType.SelectedValue);
drivers = from q in drivers
join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID
where q.CarTypeID == carTypeId
select q;
}
if (this.drpFuelType.SelectedIndex > 0)
{
byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue);
drivers = from q in drivers
where q.FuelType == fuelType
select q;
}
if (this.drpFuelCardType.SelectedIndex > 0)
{
byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue);
drivers = from q in drivers
where q.CardType == fuelCardType
select q;
}
if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text))
{
drivers = from q in drivers
where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtCarVIN.Text))
{
drivers = from q in drivers
where q.VIN.Equals(this.txtCarVIN.Text.Trim())
select q;
}
if (this.drpGender.SelectedIndex > 0)
{
byte gender = Public.ToByte(this.drpGender.SelectedValue);
drivers = from q in drivers
where q.Gender == gender
select q;
}
if (!string.IsNullOrEmpty(this.txtFirstName.Text))
{
drivers = from q in drivers
where q.FirstName.Contains(this.txtFirstName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtLastName.Text))
{
drivers = from q in drivers
where q.LastName.Contains(this.txtLastName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
{
drivers = from q in drivers
where q.NationalCode == this.txtNationalCode.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text))
{
drivers = from q in drivers
where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text))
{
drivers = from q in drivers
where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() &&
q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() &&
q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() &&
q.Alphabet == this.drpCarPlateNumber.SelectedValue
select q;
}
if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate)
{
drivers = from q in drivers
where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
select q;
}
else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
{
drivers = from q in drivers
where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
select q;
}
DataTable dtObj = new DataTable();
db.Connection.Open();
dtObj.Load(db.GetCommand(drivers).ExecuteReader());
DisposeContext();
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/drivers.mrt"));
report.RegData(dtObj);
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
public void OnRunningAccountReviewShenavarReport(Entity reportEntity, Stimulsoft.Report.StiReport report, Dictionary <string, object> parameters)
{
var dbHelper = CoreComponent.Instance.MainDbHelper;
EntityList accountReview = new EntityList();
var test = report.DataSources;
var query = string.Format("");
DataTable shenavarReportDataTable = new DataTable("ReportTable");
var reportParameterAccountReview = new Guid?();
if (parameters["AccountReview"] != null)
{
reportParameterAccountReview = Guid.Parse(parameters["AccountReview"].ToString());
}
var shenavarType = parameters["ShenavarType"];
shenavarReportDataTable.Columns.Add("ShenavarName");
shenavarReportDataTable.Columns.Add("DebtorAmount_Shenavar");
shenavarReportDataTable.Columns.Add("CreditorAmount_Shenavar");
shenavarReportDataTable.Columns.Add("AccountCode");
shenavarReportDataTable.Columns.Add("FarsiAccountReviewType_Shenavar");
shenavarReportDataTable.Columns.Add("RemainingDebtorAmount_Shenavar", typeof(decimal));
shenavarReportDataTable.Columns.Add("RemainingCreditorAmount_Shenavar", typeof(decimal));
shenavarReportDataTable.Columns.Add("FarsiShenavarType");
shenavarReportDataTable.Columns.Add("AccountReviewName_Shenavar");
for (int i = 0; i < acc.AccountReview1.Entities.Count; i++)
{
Entity accountReviewEntity = acc.AccountReview1.Entities[i];
var accountReview_Shenavar = accountReviewEntity.GetFieldValue <Guid?>("AccountReview_Shenavar");
if (reportParameterAccountReview != null)
{
if (shenavarType as string == "Person")
{
if (accountReview_Shenavar == reportParameterAccountReview)
{
if (accountReviewEntity.GetFieldValue <string>("ShenavarType") == "Person")
{
accountReview.Entities.Add(accountReviewEntity);
}
}
}
else if (shenavarType as string == "CostCenter")
{
if (accountReview_Shenavar == reportParameterAccountReview)
{
if (accountReviewEntity.GetFieldValue <string>("ShenavarType") == "CostCenter")
{
accountReview.Entities.Add(accountReviewEntity);
}
}
}
else if (shenavarType as string == "Project")
{
if (accountReview_Shenavar == reportParameterAccountReview)
{
if (accountReviewEntity.GetFieldValue <string>("ShenavarType") == "Project")
{
accountReview.Entities.Add(accountReviewEntity);
}
}
}
}
}
foreach (var entity in accountReview.Entities)
{
var row = shenavarReportDataTable.NewRow();
row["ShenavarName"] = entity.GetFieldValue <string>("ShenavarName");
row["RemainingDebtorAmount_Shenavar"] = entity.GetFieldValue <decimal>("RemainingDebtorAmount_Shenavar");
row["RemainingCreditorAmount_Shenavar"] = entity.GetFieldValue <decimal>("RemainingCreditorAmount_Shenavar");
row["FarsiAccountReviewType_Shenavar"] = entity.GetFieldValue <string>("FarsiAccountReviewType_Shenavar");
row["FarsiShenavarType"] = entity.GetFieldValue <string>("FarsiShenavarType");
row["AccountReviewName_Shenavar"] = entity.GetFieldValue <string>("AccountReviewName_Shenavar");
shenavarReportDataTable.Rows.Add(row);
}
report.Dictionary.DataSources.Clear();
report.RegData("DataSource", shenavarReportDataTable);
report.Dictionary.Synchronize();
StiDataBand dataBand1 = (StiDataBand)report.GetComponentByName("DataBand1");
dataBand1.DataSourceName = "ReportTable";
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_manr210 manr210Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtMaster;
List <YR.ERP.DAL.YRModel.Reports.Man.Manr210.Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Manr210 != null) //他窗引用時
{
manr210Model = Vw_Manr210;
}
else
{
manr210Model = DrMaster.ToItem <vw_manr210>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(manr210Model.mea01))
{
queryModel = new QueryInfo();
queryModel.TableName = "mea_tb";
queryModel.ColumnName = "mea01";
queryModel.ColumnType = TabMaster.DtSource.Columns["mea01"].DataType.Name;
queryModel.Value = manr210Model.mea01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(manr210Model.mea03))
{
queryModel = new QueryInfo();
queryModel.TableName = "mea_tb";
queryModel.ColumnName = "mea03";
queryModel.ColumnType = TabMaster.DtSource.Columns["mea03"].DataType.Name;
queryModel.Value = manr210Model.mea03;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(manr210Model.mea20))
{
queryModel = new QueryInfo();
queryModel.TableName = "mea_tb";
queryModel.ColumnName = "mea20";
queryModel.ColumnType = TabMaster.DtSource.Columns["mea20"].DataType.Name;
queryModel.Value = manr210Model.mea03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(manr210Model.mea02_s))
{
sbQuerySingle.AppendLine("AND mea02>=@mea02_s");
sqlParmList.Add(new SqlParameter("@mea02_s", manr210Model.mea02_s));
}
if (!GlobalFn.varIsNull(manr210Model.mea02_e))
{
sbQuerySingle.AppendLine("AND mea02<=@mea02_e");
sqlParmList.Add(new SqlParameter("@mea02_e", manr210Model.mea02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT
mea00,mea01,mea02,mea03,mea04,mea05,
mea06,mea07,mea08,mea09,mea10,
mea11,mea12,mea13,mea14,mea14t,
mea15,mea16,mea17,mea18,mea19,
mea20,mea21,mea22,mea23,mea24,
mea25,mea26,mea27,mea28,mea29,
bab02 as mea01_c,
pca02 as mea03_c,
bec02 as mea04_c,beb02 as mea05_c,
bef03 as mea11_c,pbb02 as mea12_c,
ica_main.ica03 as ica03_main,
'' as mea13_str,'' as mea14_str,'' as mea14t_str,
'' as mea22_str,
bej_main.bej03 as bej03_main,
meb02,meb03,meb04,meb05,meb06,
meb07,
ica_detail.ica03 as ica03_detail,
bej_detail.bej03 as bej03_detail,
'' as meb05_str
FROM mea_tb
LEFT JOIN meb_tb On mea01=meb01
LEFT JOIN pca_tb ON mea03=pca01 --廠商
LEFT JOIN bec_tb ON mea04=bec01 --員工
LEFT JOIN beb_tb ON mea05=beb01 --部門
LEFT JOIN bef_tb ON mea11=bef02 AND bef01='1' --收付款條件
LEFT JOIN pbb_tb ON mea12=pbb01 --採購取價原則
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(mea01,1,baa06)=bab01
LEFT JOIN ica_tb ica_main ON mea20=ica_main.ica01
LEFT JOIN bej_tb bej_main ON mea23=bej_main.bej01
LEFT JOIN ica_tb ica_detail ON meb03=ica_detail.ica01
LEFT JOIN bej_tb bej_detail ON meb06=bej_detail.bej01
WHERE 1=1
AND meaconf='Y'
AND mea00='2'
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>();
foreach (Master masterModel in masterList)
{
masterModel.mea22_str = string.Format("{0:N" + masterModel.bej03_main + "}", masterModel.mea22); //主件數量
masterModel.meb05_str = string.Format("{0:N" + masterModel.bej03_detail + "}", masterModel.meb05); //子件數量
var bekModel = BoBas.OfGetBekModel(masterModel.mea10);
masterModel.mea13_str = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.mea13);
masterModel.mea14_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.mea14);
masterModel.mea14t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.mea14t);
}
pReport.RegData(dtMaster);
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
//處理排序
StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
switch (manr210Model.order_by)
{
case "1": //1.依託工日期
stiDataBand1.Sort = new string[] { "ASC", "mea02" };
break;
case "2": //2.依廠商
stiDataBand1.Sort = new string[] { "ASC", "mea03" };
break;
}
//處理跳頁
StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (manr210Model.jump_yn.ToUpper() == "Y")
{
grouperBand1.NewPageAfter = true;
grouperBand1.ResetPageNumber = true;
}
else
{
grouperBand1.NewPageAfter = false;
grouperBand1.ResetPageNumber = false;
}
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_manr311 manr311Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtMaster;
List <YR.ERP.DAL.YRModel.Reports.Man.Manr311.Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Manr311 != null) //他窗引用時
{
manr311Model = Vw_Manr311;
}
else
{
manr311Model = DrMaster.ToItem <vw_manr311>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(manr311Model.mfa01))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa01";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa01"].DataType.Name;
queryModel.Value = manr311Model.mfa01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(manr311Model.mfa03))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa03";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa03"].DataType.Name;
queryModel.Value = manr311Model.mfa03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
if (!GlobalFn.varIsNull(manr311Model.mfa06))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa06";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa06"].DataType.Name;
queryModel.Value = manr311Model.mfa06;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(manr311Model.mfa02_s))
{
sbQuerySingle.AppendLine("AND mfa02>=@mfa02_s");
sqlParmList.Add(new SqlParameter("@mfa02_s", manr311Model.mfa02_s));
}
if (!GlobalFn.varIsNull(manr311Model.mfa02_e))
{
sbQuerySingle.AppendLine("AND mfa02<=@mfa02_e");
sqlParmList.Add(new SqlParameter("@mfa02_e", manr311Model.mfa02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT
mfa00,mfa01,mfa02,mfa03,mfa04,mfa05,
mfa06,mfa07,mfa08,
bab02 as mfa01_c,
pca02 as mfa03_c,
bec02 as mfa04_c,beb02 as mfa05_c,
mfb02,mfb03,mfb04,mfb05,mfb06,
mfb07,mfb09,
ica.ica03 as ica03,
bej.bej03 as bej03,
'' as mfb05_str,
pcb03,pcb04,pcb06,pcb07
FROM mfa_tb
LEFT JOIN mfb_tb On mfa01=mfb01
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(mfa01,1,baa06)=bab01
LEFT JOIN pca_tb ON mfa03=pca01 --廠商
LEFT JOIN bec_tb ON mfa04=bec01 --員工
LEFT JOIN beb_tb ON mfa05=beb01 --部門
LEFT JOIN ica_tb ica ON mfb03=ica.ica01
LEFT JOIN bej_tb bej ON mfb06=bej.bej01
LEFT JOIN pcb_tb ON mfa03=pcb01 and mfa07=pcb02
WHERE 1=1
AND mfaconf='Y'
AND mfa00='2'
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>();
foreach (Master masterModel in masterList)
{
masterModel.mfb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.mfb05);//子件數量
}
pReport.RegData(dtMaster);
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
//處理排序
StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
switch (manr311Model.order_by)
{
case "1": //1.依出庫日期
stiDataBand1.Sort = new string[] { "ASC", "mfa02" };
break;
case "2": //2.依廠商
stiDataBand1.Sort = new string[] { "ASC", "mfa03" };
break;
}
//處理跳頁
StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (manr311Model.jump_yn.ToUpper() == "Y")
{
grouperBand1.NewPageAfter = true;
grouperBand1.ResetPageNumber = true;
}
else
{
grouperBand1.NewPageAfter = false;
grouperBand1.ResetPageNumber = false;
}
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_invr401 invr401Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Invr401 != null) //他窗引用時
{
invr401Model = Vw_Invr401;
}
else
{
invr401Model = DrMaster.ToItem <vw_invr401>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr401Model.ila01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila01"].DataType.Name;
queryModel.Value = invr401Model.ila01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr401Model.ila03))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila03";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila03"].DataType.Name;
queryModel.Value = invr401Model.ila03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr401Model.ila02_s))
{
sbQuerySingle.AppendLine("AND ila02>=@ila02_s");
sqlParmList.Add(new SqlParameter("@ila02_s", invr401Model.ila02_s));
}
if (!GlobalFn.varIsNull(invr401Model.ila02_e))
{
sbQuerySingle.AppendLine("AND ila02<=@ila02_e");
sqlParmList.Add(new SqlParameter("@ila02_e", invr401Model.ila02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT ila_tb.*,
sca02 as ila03_c,
bec02 as ila04_c,
beb02 as ila05_c,
bab02 as ila01_c,
bel02 as ila14_c,
sbg1.sbg02 as ila15_c,
sbg2.sbg02 as ila16_c,
ilb02,ilb03,ilb04,ilb05,ilb06,
ilb11,ilb13,
bej03
FROM ila_tb
LEFT JOIN ilb_tb ON ila01=ilb01
LEFT JOIN sca_tb ON ila03=sca01 --客戶
LEFT JOIN bec_tb ON ila04=bec01 --員工
LEFT JOIN beb_tb ON ila05=beb01 --部門
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(ila01,1,baa06)=bab01
LEFT JOIN ica_tb ON ilb03=ica01
LEFT JOIN bej_tb ON ilb03=bej01
LEFT JOIN bel_tb ON ila14=bel01 --貨運方式
LEFT JOIN sbg_tb sbg1 ON ila15=sbg1.sbg01 --運送起點
LEFT JOIN sbg_tb sbg2 ON ila16=sbg2.sbg01 --運送終點
WHERE 1=1
AND ilaconf='Y'
";
//處理排序
switch (invr401Model.order_by)
{
case "1": //1.依借出日期
sqlOrderBy = " ORDER BY ila02";
break;
case "2": //2.依客戶
sqlOrderBy = " ORDER BY ila03";
break;
}
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05);//數量
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (invr401Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnDrivers_Click(object sender, EventArgs e)
{
db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
var query = (from p in db.Persons
join dc in db.DriverCertifications on p.PersonID equals dc.PersonID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
join pn in db.PlateNumbers on cpn.PlateNumberID equals pn.PlateNumberID
join c in db.Cars on cpn.CarID equals c.CarID
join fc in db.FuelCards on c.CarID equals fc.CarID
join j in db.Ajancies on jd.AjancyID equals j.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ur in db.UsersInRoles on jp.UserRoleID equals ur.UserRoleID
join ct in db.Cities on j.CityID equals ct.CityID
join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
ur.RoleID == (short)Public.Role.AjancyManager &&
jd.LockOutDate == null &&
dcc.LockOutDate == null &&
fc.DiscardDate == null
orderby p.LastName
select new
{
Driver = p,
Owner = new { owp.NationalCode, owp.FirstName, owp.LastName, },
DrivingLicense = p.DrivingLicenses.Count > 0 ? p.DrivingLicenses[0] : null,
j.AjancyName,
City = ct.Name,
Car = new
{
Color = c.Color,
Model = c.Model,
EngineNo = c.EngineNo,
ChassisNo = c.ChassisNo,
TypeName = c.CarType.TypeName,
FuelType = c.FuelType,
VIN = c.VIN,
TwoDigits = cpn.PlateNumber.TwoDigits,
Alphabet = cpn.PlateNumber.Alphabet,
ThreeDigits = cpn.PlateNumber.ThreeDigits,
Iran = "ایران",
RegionIdentifier = cpn.PlateNumber.RegionIdentifier,
},
ct.ProvinceID,
ct.CityID,
dc.DriverCertificationNo,
FuelCard = new { fc.CardType, fc.PAN },
j.AjancyID
});
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
query = from q in query
where q.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue)
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
query = from q in query
where q.CityID == Public.ToByte(this.drpCity.SelectedValue)
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
query = from q in query
where q.AjancyID == Public.ToInt(this.drpAjancies.SelectedValue)
select q;
}
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("OWNationalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("OWFirstName", typeof(string)));
dtObj.Columns.Add(new DataColumn("OWLastName", typeof(string)));
dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("FirstName", typeof(string)));
dtObj.Columns.Add(new DataColumn("LastName", typeof(string)));
dtObj.Columns.Add(new DataColumn("Father", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificateNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificateAlfa", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificateSerie", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificateSerial", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificatePlace", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthDate", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthPlace", typeof(string)));
dtObj.Columns.Add(new DataColumn("Gender", typeof(string)));
dtObj.Columns.Add(new DataColumn("Marriage", typeof(string)));
dtObj.Columns.Add(new DataColumn("FamilyMembersCount", typeof(string)));
dtObj.Columns.Add(new DataColumn("Education", typeof(string)));
dtObj.Columns.Add(new DataColumn("Religion", typeof(string)));
dtObj.Columns.Add(new DataColumn("Subreligion", typeof(string)));
dtObj.Columns.Add(new DataColumn("JobStatus", typeof(string)));
dtObj.Columns.Add(new DataColumn("MilitaryService", typeof(string)));
dtObj.Columns.Add(new DataColumn("Mobile", typeof(string)));
dtObj.Columns.Add(new DataColumn("Phone", typeof(string)));
dtObj.Columns.Add(new DataColumn("PostalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("Address", typeof(string)));
dtObj.Columns.Add(new DataColumn("DrivingLicenseNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("ExportDate", typeof(string)));
dtObj.Columns.Add(new DataColumn("Type", typeof(string)));
dtObj.Columns.Add(new DataColumn("ExportPlace", typeof(string)));
dtObj.Columns.Add(new DataColumn("VIN", typeof(string)));
dtObj.Columns.Add(new DataColumn("PAN", typeof(string)));
dtObj.Columns.Add(new DataColumn("CardType", typeof(string)));
dtObj.Columns.Add(new DataColumn("TypeName", typeof(string)));
dtObj.Columns.Add(new DataColumn("Model", typeof(string)));
dtObj.Columns.Add(new DataColumn("Color", typeof(string)));
dtObj.Columns.Add(new DataColumn("EngineNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("ChassisNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("FuelType", typeof(string)));
dtObj.Columns.Add(new DataColumn("TwoDigits", typeof(string)));
dtObj.Columns.Add(new DataColumn("Alphabet", typeof(string)));
dtObj.Columns.Add(new DataColumn("ThreeDigits", typeof(string)));
dtObj.Columns.Add(new DataColumn("Iran", typeof(string)));
dtObj.Columns.Add(new DataColumn("RegionIdentifier", typeof(string)));
dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string)));
dtObj.Columns.Add(new DataColumn("City", typeof(string)));
dtObj.Columns.Add(new DataColumn("DriverCertificationNo", typeof(string)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.Owner.NationalCode;
row[1] = item.Owner.FirstName;
row[2] = item.Owner.LastName;
row[3] = item.Driver.NationalCode;
row[4] = item.Driver.FirstName;
row[5] = item.Driver.LastName;
row[6] = item.Driver.Father;
row[7] = item.Driver.BirthCertificateNo;
row[8] = Public.GetAlphabet(item.Driver.BirthCertificateAlfa);
row[9] = item.Driver.BirthCertificateSerie;
row[10] = item.Driver.BirthCertificateSerial;
row[11] = item.Driver.BirthCertificatePlace;
row[12] = item.Driver.BirthDate;
row[13] = item.Driver.BirthPlace;
row[14] = item.Driver.Gender.GetValueOrDefault() == 0 ? "مرد" : "زن";
row[15] = item.Driver.Marriage.GetValueOrDefault() == 1 ? "متاهل" : "مجرد";
row[16] = item.Driver.FamilyMembersCount;
row[17] = Public.GetEducation(item.Driver.Education.GetValueOrDefault());
row[18] = Public.GetReligion(item.Driver.Religion.GetValueOrDefault());
row[19] = item.Driver.Subreligion;
row[20] = Public.GetJobStatus(item.Driver.JobStatus.GetValueOrDefault());
row[21] = Public.GetMilitaryService(item.Driver.MilitaryService.GetValueOrDefault());
row[22] = item.Driver.Mobile;
row[23] = item.Driver.Phone;
row[24] = item.Driver.PostalCode;
row[25] = item.Driver.Address;
if (item.DrivingLicense != null)
{
row[26] = item.DrivingLicense.DrivingLicenseNo;
row[27] = Public.ToPersianDate(item.DrivingLicense.ExportDate);
row[28] = Public.GetDrivingLicenseType(item.DrivingLicense.Type);
row[29] = item.DrivingLicense.ExportPlace;
}
else
{
row[26] = string.Empty;
row[27] = string.Empty;
row[28] = string.Empty;
row[29] = string.Empty;
}
row[30] = item.Car.VIN;
row[31] = item.FuelCard.PAN;
row[32] = Public.GetFuelCardTypeName((Public.FuelCardType)item.FuelCard.CardType);
row[33] = item.Car.TypeName;
row[34] = item.Car.Model;
row[35] = item.Car.Color;
row[36] = item.Car.EngineNo;
row[37] = item.Car.ChassisNo;
row[38] = Public.GetFuelTypeName((Public.FuelType)item.Car.FuelType);
row[39] = item.Car.TwoDigits;
row[40] = Public.GetAlphabet(item.Car.Alphabet);
row[41] = item.Car.ThreeDigits;
row[42] = "ایران";
row[43] = item.Car.RegionIdentifier;
row[44] = item.AjancyName;
row[45] = item.City;
row[46] = item.DriverCertificationNo;
dtObj.Rows.Add(row);
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/drivers_all.mrt"));
report.RegData(dtObj);
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
protected void btnExcel_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
int cycleFoodId = Public.ToInt(this.drpStuffs.SelectedValue);
var query = from sf in db.SchoolFoods
join cl in db.Calendars on sf.CalendarID equals cl.CalendarID
join ssl in db.SchoolSubLevels on sf.SchoolSubLevelID equals ssl.SchoolSubLevelID
join slv in db.SchoolLevels on ssl.SchoolLevelID equals slv.SchoolLevelID
join lv in db.Levels on slv.LevelID equals lv.LevelID
join s in db.Schools on slv.SchoolID equals s.SchoolID
join sl in db.SubLevels on ssl.SubLevelID equals sl.SubLevelID
join cf in db.CycleFoods on sf.CycleFoodID equals cf.CycleFoodID
join st in db.REP_Stuffs on cf.StuffID equals st.StuffID
join a in db.Areas on s.AreaCode equals a.AreaCode
orderby a.AreaCode, s.SchoolCode, sf.CalendarID
where a.ProvinceID == int.Parse(System.Web.Configuration.WebConfigurationManager.AppSettings["DefaultProvinceId"]) &&
sf.CycleFoodID == Public.ToInt(this.drpStuffs.SelectedValue) && slv.LockOutDate == null
group ssl by new
{
s.SchoolID,
s.SchoolCode,
s.SchoolName,
lv.LevelName,
a.AreaCode,
a.AreaName,
st.StuffName,
cl.SolarYear,
cl.SolarMonth,
cl.SolarDay
} into grp
orderby grp.Key.SchoolCode
select new
{
grp.Key.SchoolCode,
grp.Key.SchoolName,
grp.Key.AreaCode,
grp.Key.AreaName,
grp.Key.LevelName,
grp.Key.StuffName,
grp.Key.SolarYear,
grp.Key.SolarMonth,
RationDate = string.Concat(grp.Key.SolarYear, "/", grp.Key.SolarMonth, "/", grp.Key.SolarDay),
Ration = (from sf2 in db.SchoolFoods
join cl2 in db.Calendars on sf2.CalendarID equals cl2.CalendarID
join ssl2 in db.SchoolSubLevels on sf2.SchoolSubLevelID equals ssl2.SchoolSubLevelID
join slv2 in db.SchoolLevels on ssl2.SchoolLevelID equals slv2.SchoolLevelID
where slv2.SchoolID == grp.Key.SchoolID && slv2.LockOutDate == null &&
sf2.CycleFoodID == cycleFoodId && cl2.SolarYear == grp.Key.SolarYear &&
cl2.SolarMonth == grp.Key.SolarMonth && cl2.SolarDay == grp.Key.SolarDay
select new
{
sf2.FoodCount
}).Sum(fc => fc.FoodCount)
};
if (this.drpMonths.SelectedIndex > 0)
{
string[] monthVals = this.drpMonths.SelectedValue.Split('|');
short solarYear = Public.ToShort(monthVals[0]);
byte solarMonth = Public.ToByte(monthVals[1]);
query = from q in query
where q.SolarYear == solarYear && q.SolarMonth == solarMonth
select q;
}
if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
{
query = from q in query
where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
select q;
}
if (!string.IsNullOrEmpty(this.txtSchoolCode.Text))
{
query = from q in query
where q.SchoolCode == Public.ToInt(this.txtSchoolCode.Text)
select q;
}
DataTable dtObj = new DataTable();
db.Connection.Open();
dtObj.Load(db.GetCommand(query).ExecuteReader());
db.Connection.Close();
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData(dtObj);
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch_ration.mrt"));
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_invr302 invr302Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Invr302 != null) //他窗引用時
{
invr302Model = Vw_Invr302;
}
else
{
invr302Model = DrMaster.ToItem <vw_invr302>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr302Model.iga01))
{
queryModel = new QueryInfo();
queryModel.TableName = "iga_tb";
queryModel.ColumnName = "iga01";
queryModel.ColumnType = TabMaster.DtSource.Columns["iga01"].DataType.Name;
queryModel.Value = invr302Model.iga01;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr302Model.iga02_s))
{
sbQuerySingle.AppendLine("AND iga02>=@iga02_s");
sqlParmList.Add(new SqlParameter("@iga02_s", invr302Model.iga02_s));
}
if (!GlobalFn.varIsNull(invr302Model.iga02_e))
{
sbQuerySingle.AppendLine("AND iga02<=@iga02_e");
sqlParmList.Add(new SqlParameter("@iga02_e", invr302Model.iga02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT iga_tb.*,
bec02 as iga03_c,
beb02 as iga04_c,
bab02 as iga01_c,
igb02,igb03,igb04,igb05,igb06,
igb09,igb10,
ica03
FROM iga_tb
LEFT JOIN igb_tb ON iga01=igb01
LEFT JOIN bec_tb ON iga03=bec01 --員工
LEFT JOIN beb_tb ON iga04=beb01 --部門
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(iga01,1,baa06)=bab01
LEFT JOIN ica_tb ON igb03=ica01
WHERE 1=1
AND igaconf='Y'
AND iga00='2'
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
var bejModel = BoBas.OfGetBejModel(masterModel.igb06);
if (bejModel != null)
{
masterModel.igb05_str = string.Format("{0:N" + bejModel.bej03 + "}", masterModel.igb05);//數量
}
else
{
masterModel.igb05_str = masterModel.igb05.ToString();
}
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (invr302Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
private void printGovahi(string type)
{
string CodeMeli = "", NameReshte = "";
string sqlGovahi = "";
string str = Application.StartupPath + "\\rep\\govarpt.mrt";
Stimulsoft.Report.StiReport stikol = new Stimulsoft.Report.StiReport();
try
{
stikol.Load(str);
}
catch (Exception ex)
{
FMessegeBox.FarsiMessegeBox.Show(ex.Message);
}
if (type == "one")
{
CodeMeli = dataGridViewX1.CurrentRow.Cells[0].Value.ToString();
NameReshte = comboreshte1.Text;
sqlGovahi = "SELECT person.codeMeli AS meli, person.name + ' ' + person.family AS name, person.father, person.shsh, person.tarikht AS tavalod, person.sadere, [P&R].nomreA, " +
"[P&R].nomreN, [P&R].dateSodoor AS sodoor, [P&R].shGovahi AS govahi, person.mtavalod AS shahr, [P&R].RID AS codeStandard FROM person INNER JOIN [P&R] ON person.codeMeli = [P&R].PID " +
"WHERE (person.codeMeli = N'" + CodeMeli + "') AND ([P&R].Rname = N'" + dataGridViewX1.CurrentRow.Cells[12].Value.ToString() +
"')AND([P&R].Ronvan = N'" + dataGridViewX1.CurrentRow.Cells[11].Value.ToString() + "')";
//sqlGovahi = "SELECT person.codeMeli[meli], person.name + ' ' + person.family[name], person.father, person.shsh, person.tarikht[tavalod], person.sadere, [P&R].nomreA, " +
// "[P&R].nomreN, [P&R].dateSodoor[sodoor], [P&R].shGovahi[govahi], reshte.onvan, reshte.date[azmon], reshte.modat, reshte.code[codeStandard], person.mtavalod[shahr] " +
// "FROM person INNER JOIN [P&R] ON person.codeMeli = [P&R].PID INNER JOIN reshte ON [P&R].RID = reshte.code " +
// "WHERE (person.codeMeli = N'" + CodeMeli + "') AND ([P&R].Rname = N'" + comboBoxEx1.SelectedItem.ToString() +
// "')AND([P&R].Ronvan = N'" + comboBoxEx2.Text + "')";
DataManagement.DT = DataManagement.Search(sqlGovahi);
try
{
stikol.RegData("variable", DataManagement.DT);
DataManagement.DT.Columns.Add("mahal");
DataManagement.DT.Columns.Add("onvan");
DataManagement.DT.Columns.Add("azmon");
DataManagement.DT.Columns.Add("modat");
DataManagement.DT.Rows[0]["mahal"] = txtNameMarkaz.Text;
DataManagement.DT.Rows[0]["onvan"] = txtOnvan.Text;
DataManagement.DT.Rows[0]["azmon"] = txtAzmoon.Text;
DataManagement.DT.Rows[0]["modat"] = txtModat.Text;
stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
stikol.Show(this);
}
catch (Exception ex)
{
FMessegeBox.FarsiMessegeBox.Show(ex.Message, "اخطار");
}
}/*
* else
* {
* NameReshte = comboBoxEx1.Text;
*
* for (int i = 0; i< dataGridViewX1.RowCount; i++)
* {
* CodeMeli = dataGridViewX1.Rows[i].Cells[0].Value.ToString();
* sqlGovahi = "SELECT person.codeMeli[meli], person.name + ' ' + person.family[name], person.father, person.shsh, person.tarikht[tavalod], person.sadere, [P&R].nomreA, " +
* "[P&R].nomreN, [P&R].dateSodoor[sodoor], [P&R].shGovahi[govahi], reshte.onvan, reshte.date[azmon], reshte.modat, reshte.code[codeStandard], person.mtavalod[shahr] " +
* "FROM person INNER JOIN [P&R] ON person.codeMeli = [P&R].PID INNER JOIN reshte ON [P&R].RID = reshte.code " +
* "WHERE (person.codeMeli = N'" + CodeMeli + "') AND (reshte.name = N'" + NameReshte + "')";
*
* DataManagement.DT = DataManagement.Search(sqlGovahi);
* try
* {
*
* DataManagement.DT.Columns.Add("mahal");
* DataManagement.DT.Columns.Add("onvan");
* DataManagement.DT.Columns.Add("azmon");
* DataManagement.DT.Columns.Add("modat");
* DataManagement.DT.Rows[0]["mahal"] = txtNameMarkaz.Text;
* DataManagement.DT.Rows[0]["onvan"] = txtOnvan.Text;
* DataManagement.DT.Rows[0]["azmon"] = txtAzmoon.Text;
* DataManagement.DT.Rows[0]["modat"] = txtModat.Text;
*
* stikol.RegData("variable", DataManagement.DT);
* stikol.Dictionary.DataSources.Items[0].DataTable = DataManagement.DT;
*
* stikol.Print();
* }
* catch(Exception ex)
* {
* FMessegeBox.FarsiMessegeBox.Show(ex.Message, "اخطار");
* break;
* }
* }
* }*/
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_invr410 invr410Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtIlaTb;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
List <Master> resultList = null;
try
{
invr410Model = DrMaster.ToItem <vw_invr410>();
resultList = new List <Master>();
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr410Model.ila03))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila03";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila03"].DataType.Name;
queryModel.Value = invr410Model.ila03;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr410Model.ila04))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila04";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila04"].DataType.Name;
queryModel.Value = invr410Model.ila04;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr410Model.ila05))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila05";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila05"].DataType.Name;
queryModel.Value = invr410Model.ila05;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr410Model.ila02_s))
{
sbQuerySingle.AppendLine("AND ila02>=@ila02_s");
sqlParmList.Add(new SqlParameter("@ila02_s", invr410Model.ila02_s));
}
if (!GlobalFn.varIsNull(invr410Model.ila02_e))
{
sbQuerySingle.AppendLine("AND ila02<=@ila02_e");
sqlParmList.Add(new SqlParameter("@ila02_e", invr410Model.ila02_e));
}
if (invr410Model.conf_yn != "0")
{
if (invr410Model.conf_yn == "1")
{
sbQuerySingle.AppendLine("AND ilaconf = 'Y'");
}
if (invr410Model.conf_yn == "2")
{
sbQuerySingle.AppendLine("AND ilaconf = 'N'");
}
}
if (invr410Model.close_yn != "0")
{
if (invr410Model.close_yn == "1")//未結案
{
sbQuerySingle.AppendLine("AND (ilb05-ilb15) >0 ");
}
if (invr410Model.close_yn == "2")//已結案
{
sbQuerySingle.AppendLine("AND (ilb05-ilb15) <=0 ");
}
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"
SELECT
ila01,ila02,ila03,sca03 as ila03_c ,
ilb02,ila04,bec02 AS ila04_c,ila05,beb03 AS ila05_c,
ilb03,ilb04,ilb05,'' as ilb05_str,ilb06,
bej03,ilb11,
ilb15,'' as ilb15_str,
ilb16
FROM ila_tb
LEFT JOIN ilb_tb ON ila01=ilb01
LEFT JOIN sca_tb ON ila03=sca01
LEFT JOIN bec_tb ON ila04=bec01
LEFT JOIN beb_tb ON ila05=beb01
LEFT JOIN bej_tb ON ilb06=bej01
WHERE ilaconf='Y'
AND ilb01 IS NOT NULL
";
dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtIlaTb.TableName = "Master";
if (dtIlaTb != null)
{
resultList.AddRange(dtIlaTb.ToList <Master>());
}
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
foreach (Master masterModel in resultList)
{
//數量處理
masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05); //數量
masterModel.ilb15_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb15); //數量
}
pReport.RegData("master", resultList);
pReport.CacheAllData = true;
//處理排序--這裡利用group來達到
StiGroupHeaderBand stiGroupHeaderBand1 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand1"];
switch (invr410Model.order_by)
{
case "1": //1.依出庫日期
stiGroupHeaderBand1.Condition.Value = "{Master.ila02}";
break;
case "2": //2.依客戶
stiGroupHeaderBand1.Condition.Value = "{Master.ila03}";
break;
case "3": //3.依部門
stiGroupHeaderBand1.Condition.Value = "{Master.ila05}";
break;
case "4": //4.依業務
stiGroupHeaderBand1.Condition.Value = "{Master.ila04}";
break;
case "5": //4.依出庫倉
stiGroupHeaderBand1.Condition.Value = "{Master.ila11}";
break;
}
//處理跳頁
StiDataBand dataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
if (invr410Model.jump_yn.ToUpper() == "Y")
{
dataBand1.NewPageAfter = true;
dataBand1.ResetPageNumber = true;
}
else
{
dataBand1.NewPageAfter = false;
dataBand1.ResetPageNumber = false;
}
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
var query = from ins in db.Insurances
join dc in db.DriverCertifications on ins.DriverCertificationID equals dc.DriverCertificationID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
join pl in db.PlateNumbers on cpn.PlateNumberID equals pl.PlateNumberID
join c in db.Cars on cpn.CarID equals c.CarID
join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID
join fc in db.FuelCards on c.CarID equals fc.CarID
join p in db.Persons on dc.PersonID equals p.PersonID
join u in db.Users on p.PersonID equals u.PersonID
join ct in db.Cities on u.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
where dcc.LockOutDate == null && ins.CancelInsurance == Convert.ToBoolean(this.drpCancelInsurance.SelectedIndex)
orderby p.LastName
select new
{
p.PersonID,
p.FirstName,
p.LastName,
p.Father,
p.NationalCode,
p.BirthCertificateNo,
p.BirthDate,
u.CityID,
u.ProvinceID,
City = ct.Name,
Province = pv.Name,
c.FuelType,
crt.TypeName,
crt.Capacity,
c.VIN,
cpn.OwnerPersonID,
pl.TwoDigits,
pl.ThreeDigits,
pl.Alphabet,
pl.RegionIdentifier,
fc.PAN,
ins.InsuranceNo,
ins.BranchNo,
ins.SubmitDate
};
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
query = from q in query
where q.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue)
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
query = from q in query
where q.CityID == Public.ToShort(this.drpCity.SelectedValue)
select q;
}
if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate)
{
query = from q in query
where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
select q;
}
else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
{
query = from q in query
where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
select q;
}
if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
{
query = from q in query
where q.NationalCode == this.txtNationalCode.Text.Trim()
select q;
}
DataTable dtObj = new DataTable();
dtObj.Columns.Add(new DataColumn("FirstName", typeof(string)));
dtObj.Columns.Add(new DataColumn("LastName", typeof(string)));
dtObj.Columns.Add(new DataColumn("Father", typeof(string)));
dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthCertificateNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("BirthDate", typeof(string)));
dtObj.Columns.Add(new DataColumn("InsuranceNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("BranchNo", typeof(string)));
dtObj.Columns.Add(new DataColumn("VIN", typeof(string)));
dtObj.Columns.Add(new DataColumn("PAN", typeof(string)));
dtObj.Columns.Add(new DataColumn("TypeName", typeof(string)));
dtObj.Columns.Add(new DataColumn("FuelType", typeof(string)));
dtObj.Columns.Add(new DataColumn("TwoDigits", typeof(string)));
dtObj.Columns.Add(new DataColumn("Alphabet", typeof(string)));
dtObj.Columns.Add(new DataColumn("ThreeDigits", typeof(string)));
dtObj.Columns.Add(new DataColumn("Iran", typeof(string)));
dtObj.Columns.Add(new DataColumn("RegionIdentifier", typeof(string)));
dtObj.Columns.Add(new DataColumn("City", typeof(string)));
dtObj.Columns.Add(new DataColumn("Province", typeof(string)));
dtObj.Columns.Add(new DataColumn("Ownership", typeof(string)));
dtObj.Columns.Add(new DataColumn("WorkType", typeof(string)));
dtObj.Columns.Add(new DataColumn("Scope", typeof(string)));
dtObj.Columns.Add(new DataColumn("Capacity", typeof(string)));
foreach (var item in query)
{
DataRow row = dtObj.NewRow();
row[0] = item.FirstName;
row[1] = item.LastName;
row[2] = item.Father;
row[3] = item.NationalCode;
row[4] = item.BirthCertificateNo;
row[5] = Public.ToPersianDate(item.BirthDate);
row[6] = item.InsuranceNo;
row[7] = item.BranchNo;
row[8] = item.VIN;
row[9] = item.PAN;
row[10] = item.TypeName;
row[11] = Public.GetFuelTypeName((Public.FuelType)item.FuelType);
row[12] = item.TwoDigits;
row[13] = Public.GetAlphabet(item.Alphabet);
row[14] = item.ThreeDigits;
row[15] = "ایران";
row[16] = item.RegionIdentifier;
row[17] = item.City;
row[18] = item.Province;
row[19] = item.OwnerPersonID == item.PersonID ? "مالک" : "بهره بردار";
row[20] = "آژانس";
row[21] = "درون شهری";
row[22] = item.Capacity;
dtObj.Rows.Add(row);
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData(dtObj);
report.Load(System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/Insurance.mrt"));
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_cspr110 cspr110Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange = "", strWhere = "";
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
//if (Vw_Invr302 != null) //他窗引用時
// cspr110Model = Vw_Invr302;
//else
// cspr110Model = DrMaster.ToItem<vw_invr302>();
cspr110Model = DrMaster.ToItem <vw_cspr110>();
queryInfoList = new List <QueryInfo>();
#region range 處理
//if (!GlobalFn.varIsNull(cspr110Model.iga01))
//{
// queryModel = new QueryInfo();
// queryModel.TableName = "iga_tb";
// queryModel.ColumnName = "iga01";
// queryModel.ColumnType = TabMaster.DtSource.Columns["iga01"].DataType.Name;
// queryModel.Value = cspr110Model.iga01;
// queryInfoList.Add(queryModel);
//}
sqlParmList = new List <SqlParameter>();
//strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
sqlParmList = new List <SqlParameter>();
if (!GlobalFn.varIsNull(cspr110Model.jja16_s))
{
//sbQuerySingle.AppendLine("AND jja16>=@jja16_s");
sqlParmList.Add(new SqlParameter("@jja16_s", cspr110Model.jja16_s));
}
if (!GlobalFn.varIsNull(cspr110Model.jja16_e))
{
//sbQuerySingle.AppendLine("AND jja16<=@jja16_e");
sqlParmList.Add(new SqlParameter("@jja16_e", cspr110Model.jja16_e));
}
if (!GlobalFn.varIsNull(cspr110Model.icc05_s))
{
//sbQuerySingle.AppendLine("AND icc05>=@icc05_s");
sqlParmList.Add(new SqlParameter("@icc05_s", cspr110Model.icc05_s));
}
if (!GlobalFn.varIsNull(cspr110Model.icc05_e))
{
//sbQuerySingle.AppendLine("AND icc05>=@icc05_e");
sqlParmList.Add(new SqlParameter("@icc05_e", cspr110Model.icc05_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"
select a.*,b.icc05
from
(
select
jja04,jja05
from jja_tb
where jja23='Y'
AND jja16>=@jja16_s
AND jja16<=@jja16_e
group by jja04,jja05
) a
left join icc_tb b on a.jja04=b.icc01
where
icc05>=@icc05_s
AND icc05<=@icc05_e
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
//foreach (Master masterModel in masterList)
//{
// var bejModel = BoBas.OfGetBejModel(masterModel.igb06);
// if (bejModel != null)
// masterModel.igb05_str = string.Format("{0:N" + bejModel.bej03 + "}", masterModel.igb05);//數量
// else
// masterModel.igb05_str = masterModel.igb05.ToString();
//}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
//////處理跳頁
//StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
//if (cspr110Model.jump_yn.ToUpper() == "Y")
//{
// footerBand1.NewPageAfter = true;
// footerBand1.ResetPageNumber = true;
//}
//else
//{
// footerBand1.NewPageAfter = false;
// footerBand1.ResetPageNumber = false;
//}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
private void TimeSheetReport_Click(object sender, RoutedEventArgs e)
{
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.RegData("File", timeSheetDataSet);
report.Load("reports\\TimeSheet_TimeSheetReport__Time and Expense by File Number.mrt");
report.ShowWithWpf();
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_invr215 invr215Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtMaster;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
List <Master> resultList = null;
try
{
invr215Model = DrMaster.ToItem <vw_invr215>();
resultList = new List <Master>();
#region 處理類型
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr215Model.icc01))
{
queryModel = new QueryInfo();
queryModel.TableName = "icc_tb";
queryModel.ColumnName = "icc01";
queryModel.ColumnType = TabMaster.DtSource.Columns["icc01"].DataType.Name;
queryModel.Value = invr215Model.icc01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr215Model.icc02))
{
queryModel = new QueryInfo();
queryModel.TableName = "icc_tb";
queryModel.ColumnName = "icc02";
queryModel.ColumnType = TabMaster.DtSource.Columns["icc02"].DataType.Name;
queryModel.Value = invr215Model.icc02;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
sbQuerySingle = new StringBuilder();
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
if (invr215Model.show_warehouse_yn == "Y")
{
sqlBody = @"
SELECT
icc01,icc02,icc03,icc05,
ica02,ica03,ica07,
bej03,'' as icc05_str
FROM icc_tb
LEFT JOIN ica_tb ON icc01=ica01
LEFT JOIN bej_tb ON ica07=bej01
WHERE ica05>0
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
}
else
{
sqlBody = @"
SELECT
icc01,'' AS icc02,icc03,sum(icc05) as icc05,
ica02,ica03,ica07,
bej03,'' as icc05_str
FROM icc_tb
LEFT JOIN ica_tb ON icc01=ica01
LEFT JOIN bej_tb ON ica07=bej01
WHERE ica05>0
";
var sqlGroupBy = @"
GROUP BY
icc01,icc03,
ica02,ica03,ica07,
bej03";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlGroupBy), sqlParmList.ToArray());
}
dtMaster.TableName = "Master";
if (dtMaster != null)
{
resultList.AddRange(dtMaster.ToList <Master>());
}
#endregion
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
foreach (Master masterModel in resultList)
{
//數量處理
masterModel.icc05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.icc05);//數量
}
pReport.RegData("master", resultList);
pReport.CacheAllData = true;
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
DataTable dtObj = new DataTable();
db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
switch (this.drpStatus.SelectedIndex)
{
case 0: // All
var query = (from p in db.Persons
join dc in db.DriverCertifications on p.PersonID equals dc.PersonID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty()
from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty()
join c in db.Cars on cpn.CarID equals c.CarID
join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID
join fc in db.FuelCards on c.CarID equals fc.CarID
join j in db.Ajancies on jd.AjancyID equals j.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ct in db.Cities on j.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue)
orderby pv.Name, ct.Name
select new
{
OWNationalCode = owp.NationalCode,
OWFirstName = owp.FirstName,
OWLastName = owp.LastName,
p.PersonID,
p.FirstName,
p.LastName,
p.Father,
p.NationalCode,
p.BirthCertificateNo,
p.BirthCertificatePlace,
p.BirthDate,
p.Marriage,
p.BirthPlace,
p.Mobile,
p.Phone,
p.PostalCode,
p.Address,
p.Gender,
p.SubmitDate,
j.AjancyID,
j.AjancyName,
ct.CityID,
ct.ProvinceID,
Province = pv.Name,
City = ct.Name,
dc.DriverCertificationNo,
c.CarTypeID,
c.FuelType,
c.Model,
c.EngineNo,
c.ChassisNo,
crt.TypeName,
c.VIN,
ZCityID = (short?)zpn.CityID,
ZNumber = zpn.Number,
pln.TwoDigits,
pln.ThreeDigits,
pln.Alphabet,
pln.RegionIdentifier,
fc.CardType,
fc.PAN
}).Distinct();
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
byte provinceId = Public.ToByte(this.drpProvince.SelectedValue);
query = from q in query
where q.ProvinceID == provinceId
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
short cityId = Public.ToShort(this.drpCity.SelectedValue);
query = from q in query
where q.CityID == cityId
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue);
query = from q in query
where q.AjancyID == ajancyId
select q;
}
if (this.drpDriverCertification.SelectedIndex == 1)
{
query = from q in query
where q.DriverCertificationNo != null
select q;
}
else if (this.drpDriverCertification.SelectedIndex == 2)
{
query = from q in query
where q.DriverCertificationNo == null
select q;
}
if (this.drpDrivingLicenseType.SelectedIndex > 0)
{
byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue);
query = from q in query
join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID
where dl.Type == drivingLicense
select q;
}
if (this.drpMarriage.SelectedIndex > 0)
{
byte marriage = Public.ToByte(this.drpMarriage.SelectedValue);
query = from q in query
where q.Marriage == marriage
select q;
}
if (this.drpCarType.SelectedIndex > 0)
{
short carTypeId = Public.ToShort(this.drpCarType.SelectedValue);
query = from q in query
join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID
where q.CarTypeID == carTypeId
select q;
}
if (this.drpFuelType.SelectedIndex > 0)
{
byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue);
query = from q in query
where q.FuelType == fuelType
select q;
}
if (this.drpFuelCardType.SelectedIndex > 0)
{
byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue);
query = from q in query
where q.CardType == fuelCardType
select q;
}
if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text))
{
query = from q in query
where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtCarVIN.Text))
{
query = from q in query
where q.VIN.Equals(this.txtCarVIN.Text.Trim())
select q;
}
if (this.drpGender.SelectedIndex > 0)
{
byte gender = Public.ToByte(this.drpGender.SelectedValue);
query = from q in query
where q.Gender == gender
select q;
}
if (!string.IsNullOrEmpty(this.txtFirstName.Text))
{
query = from q in query
where q.FirstName.Contains(this.txtFirstName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtLastName.Text))
{
query = from q in query
where q.LastName.Contains(this.txtLastName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
{
query = from q in query
where q.NationalCode == this.txtNationalCode.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text))
{
query = from q in query
where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text))
{
query = from q in query
where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() &&
q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() &&
q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() &&
q.Alphabet == this.drpCarPlateNumber.SelectedValue
select q;
}
if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate)
{
query = from q in query
where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
select q;
}
else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
{
query = from q in query
where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
select q;
}
db.Connection.Open();
dtObj.Load(db.GetCommand(query).ExecuteReader());
db.Connection.Close();
db.Dispose();
break;
case 1: // Actives
var query1 = (from p in db.Persons
join dc in db.DriverCertifications on p.PersonID equals dc.PersonID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty()
from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty()
join c in db.Cars on cpn.CarID equals c.CarID
join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID
join fc in db.FuelCards on c.CarID equals fc.CarID
join j in db.Ajancies on jd.AjancyID equals j.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ct in db.Cities on j.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
jd.LockOutDate == null &&
dcc.LockOutDate == null
orderby pv.Name, ct.Name
select new
{
OWNationalCode = owp.NationalCode,
OWFirstName = owp.FirstName,
OWLastName = owp.LastName,
p.PersonID,
p.FirstName,
p.LastName,
p.Father,
p.NationalCode,
p.BirthCertificateNo,
p.BirthCertificatePlace,
p.BirthDate,
p.Marriage,
p.BirthPlace,
p.Mobile,
p.Phone,
p.PostalCode,
p.Address,
p.Gender,
p.SubmitDate,
j.AjancyID,
j.AjancyName,
ct.CityID,
ct.ProvinceID,
Province = pv.Name,
City = ct.Name,
dc.DriverCertificationNo,
c.CarTypeID,
c.FuelType,
c.Model,
c.EngineNo,
c.ChassisNo,
crt.TypeName,
c.VIN,
ZCityID = (short?)zpn.CityID,
ZNumber = zpn.Number,
pln.TwoDigits,
pln.ThreeDigits,
pln.Alphabet,
pln.RegionIdentifier,
fc.CardType,
fc.PAN
}).Distinct();
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
byte provinceId = Public.ToByte(this.drpProvince.SelectedValue);
query1 = from q in query1
where q.ProvinceID == provinceId
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
short cityId = Public.ToShort(this.drpCity.SelectedValue);
query1 = from q in query1
where q.CityID == cityId
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue);
query1 = from q in query1
where q.AjancyID == ajancyId
select q;
}
if (this.drpDriverCertification.SelectedIndex == 1)
{
query1 = from q in query1
where q.DriverCertificationNo != null
select q;
}
else if (this.drpDriverCertification.SelectedIndex == 2)
{
query1 = from q in query1
where q.DriverCertificationNo == null
select q;
}
if (this.drpDrivingLicenseType.SelectedIndex > 0)
{
byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue);
query1 = from q in query1
join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID
where dl.Type == drivingLicense
select q;
}
if (this.drpMarriage.SelectedIndex > 0)
{
byte marriage = Public.ToByte(this.drpMarriage.SelectedValue);
query1 = from q in query1
where q.Marriage == marriage
select q;
}
if (this.drpCarType.SelectedIndex > 0)
{
short carTypeId = Public.ToShort(this.drpCarType.SelectedValue);
query1 = from q in query1
join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID
where q.CarTypeID == carTypeId
select q;
}
if (this.drpFuelType.SelectedIndex > 0)
{
byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue);
query1 = from q in query1
where q.FuelType == fuelType
select q;
}
if (this.drpFuelCardType.SelectedIndex > 0)
{
byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue);
query1 = from q in query1
where q.CardType == fuelCardType
select q;
}
if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text))
{
query1 = from q in query1
where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtCarVIN.Text))
{
query1 = from q in query1
where q.VIN.Equals(this.txtCarVIN.Text.Trim())
select q;
}
if (this.drpGender.SelectedIndex > 0)
{
byte gender = Public.ToByte(this.drpGender.SelectedValue);
query1 = from q in query1
where q.Gender == gender
select q;
}
if (!string.IsNullOrEmpty(this.txtFirstName.Text))
{
query1 = from q in query1
where q.FirstName.Contains(this.txtFirstName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtLastName.Text))
{
query1 = from q in query1
where q.LastName.Contains(this.txtLastName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
{
query1 = from q in query1
where q.NationalCode == this.txtNationalCode.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text))
{
query1 = from q in query1
where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text))
{
query1 = from q in query1
where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() &&
q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() &&
q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() &&
q.Alphabet == this.drpCarPlateNumber.SelectedValue
select q;
}
if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate)
{
query1 = from q in query1
where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
select q;
}
else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
{
query1 = from q in query1
where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
select q;
}
db.Connection.Open();
dtObj.Load(db.GetCommand(query1).ExecuteReader());
db.Connection.Close();
db.Dispose();
break;
case 2: // Inactives
var query2 = (from p in db.Persons
join dc in db.DriverCertifications on p.PersonID equals dc.PersonID
join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID
join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty()
from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty()
join c in db.Cars on cpn.CarID equals c.CarID
join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID
join fc in db.FuelCards on c.CarID equals fc.CarID
join j in db.Ajancies on jd.AjancyID equals j.AjancyID
join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID
join ct in db.Cities on j.CityID equals ct.CityID
join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
jd.LockOutDate != null &&
dcc.LockOutDate != null
orderby pv.Name, ct.Name
select new
{
OWNationalCode = owp.NationalCode,
OWFirstName = owp.FirstName,
OWLastName = owp.LastName,
p.PersonID,
p.FirstName,
p.LastName,
p.Father,
p.NationalCode,
p.BirthCertificateNo,
p.BirthCertificatePlace,
p.BirthDate,
p.Marriage,
p.BirthPlace,
p.Mobile,
p.Phone,
p.PostalCode,
p.Address,
p.Gender,
p.SubmitDate,
j.AjancyID,
j.AjancyName,
ct.CityID,
ct.ProvinceID,
Province = pv.Name,
City = ct.Name,
dc.DriverCertificationNo,
c.CarTypeID,
c.FuelType,
c.Model,
c.EngineNo,
c.ChassisNo,
crt.TypeName,
c.VIN,
ZCityID = (short?)zpn.CityID,
ZNumber = zpn.Number,
pln.TwoDigits,
pln.ThreeDigits,
pln.Alphabet,
pln.RegionIdentifier,
fc.CardType,
fc.PAN
}).Distinct();
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province
{
byte provinceId = Public.ToByte(this.drpProvince.SelectedValue);
query2 = from q in query2
where q.ProvinceID == provinceId
select q;
}
if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city
{
short cityId = Public.ToShort(this.drpCity.SelectedValue);
query2 = from q in query2
where q.CityID == cityId
select q;
}
if (this.drpAjancies.SelectedIndex > 0)
{
int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue);
query2 = from q in query2
where q.AjancyID == ajancyId
select q;
}
if (this.drpDriverCertification.SelectedIndex == 1)
{
query2 = from q in query2
where q.DriverCertificationNo != null
select q;
}
else if (this.drpDriverCertification.SelectedIndex == 2)
{
query2 = from q in query2
where q.DriverCertificationNo == null
select q;
}
if (this.drpDrivingLicenseType.SelectedIndex > 0)
{
byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue);
query2 = from q in query2
join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID
where dl.Type == drivingLicense
select q;
}
if (this.drpMarriage.SelectedIndex > 0)
{
byte marriage = Public.ToByte(this.drpMarriage.SelectedValue);
query2 = from q in query2
where q.Marriage == marriage
select q;
}
if (this.drpCarType.SelectedIndex > 0)
{
short carTypeId = Public.ToShort(this.drpCarType.SelectedValue);
query2 = from q in query2
join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID
where q.CarTypeID == carTypeId
select q;
}
if (this.drpFuelType.SelectedIndex > 0)
{
byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue);
query2 = from q in query2
where q.FuelType == fuelType
select q;
}
if (this.drpFuelCardType.SelectedIndex > 0)
{
byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue);
query2 = from q in query2
where q.CardType == fuelCardType
select q;
}
if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text))
{
query2 = from q in query2
where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtCarVIN.Text))
{
query2 = from q in query2
where q.VIN.Equals(this.txtCarVIN.Text.Trim())
select q;
}
if (this.drpGender.SelectedIndex > 0)
{
byte gender = Public.ToByte(this.drpGender.SelectedValue);
query2 = from q in query2
where q.Gender == gender
select q;
}
if (!string.IsNullOrEmpty(this.txtFirstName.Text))
{
query2 = from q in query2
where q.FirstName.Contains(this.txtFirstName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtLastName.Text))
{
query2 = from q in query2
where q.LastName.Contains(this.txtLastName.Text.Trim())
select q;
}
if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
{
query2 = from q in query2
where q.NationalCode == this.txtNationalCode.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text))
{
query2 = from q in query2
where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim()
select q;
}
if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text))
{
query2 = from q in query2
where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() &&
q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() &&
q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() &&
q.Alphabet == this.drpCarPlateNumber.SelectedValue
select q;
}
if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate)
{
query2 = from q in query2
where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
select q;
}
else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
{
query2 = from q in query2
where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
select q;
}
db.Connection.Open();
dtObj.Load(db.GetCommand(query2).ExecuteReader());
db.Connection.Close();
db.Dispose();
break;
}
dtObj.TableName = "dt";
Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/drivers.mrt"));
report.RegData(dtObj);
report.Render();
Public.ExportInfo(3, report);
report.Dispose();
}
